用这种难度的题目面试程序员，给多少时间合适？

[lihongming]

Julia conducted a 15 days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016.

Write a query to print total number of unique hackers who made at least submission each day (starting on the first day of the contest), and find the hacker_id and name of the hacker who made maximum number of submissions each day. If more than one such hacker has a maximum number of submissions, print the lowest hacker_id. The query should print this information for each day of the contest, sorted by the date.

---

Input Format

The following tables hold contest data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.

  

• Submissions: The submission_date is the date of the submission, submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, and score is the score of the submission.

  

---

Sample Input

For the following sample input, assume that the end date of the contest was March 06, 2016.

• Hackers table

  

• Submissions table

  

---

Sample Output

2016-03-01 4 20703 Angela 2016-03-02 2 79722 Michael 2016-03-03 2 20703 Angela 2016-03-04 2 20703 Angela 2016-03-05 1 36396 Frank 2016-03-06 1 20703 Angela

Explanation

On March 01, 2016 hackers 20703, 36396, 53473 and 79722 made submissions. There are 4 unique hackers who made at least one submission each day. As each hacker made one submission, 20703 is considered to be the hacker who made maximum number of submissions on this day. The name of the hacker is Angela.

On March 02, 2016 hackers 15758, 20703 and 79722 made submissions. Now 20703 and 79722 were the only ones to submit every day, so there are 2 unique hackers who made at least one submission each day. 79722 made 2 submissions, and name of the hacker is Michael.

On March 03, 2016 hackers 20703, 36396 and 79722 made submissions. Now 20703 and 79722 were the only ones, so there are 2 unique hackers who made at least one submission each day. As each hacker made one submission so 20703 is considered to be the hacker who made maximum number of submissions on this day. The name of the hacker is Angela.

On March 04, 2016 hackers 20703, 44065, 53473 and 79722 made submissions. Now 20703 and 79722 only submitted each day, so there are 2 unique hackers who made at least one submission each day. As each hacker made one submission so 20703 is considered to be the hacker who made maximum number of submissions on this day. The name of the hacker is Angela.

On March 05, 2016 hackers 20703, 36396, 38289 and 62529 made submissions. Now 20703 only submitted each day, so there is only 1 unique hacker who made at least one submission each day. 36396 made 2 submissions and name of the hacker is Frank.

On March 06, 2016 only 20703 made submission, so there is only 1 unique hacker who made at least one submission each day. 20703 made 1 submission and name of the hacker is Angela.

[lihongming]

奇了个怪了，table 为何不能正确解析？

[msg7086]

面程序员还是面数据相关岗位？

[lihongming]

@msg7086 程序员，除了这个还有算法题，时间另计

[way2explore2]

取决于什么条件。

如果是现场做，最多给两个小时。

如果带回家，我会周一发，然后让周五交。

[msg7086]

@lihongming 面程序员的话我压根不会出 SQL 的题。
如果是面数据库方向的话，更多的会放在讨论表结构设计，索引优化这一块吧。
我最近几年的工作都不需要写 SQL 操作数据库。

[lihongming]

@msg7086 问题是这事咱俩说了不算啊。

现在就是要考 SQL 了，现场给题现场做，允许 Google，只能想想给多少时间合适了。

[sealingpp]

这全英文的,薪资范围多少,针对 什么级别的

[lihongming]

@way2explore2 两个小时……那看来现场考不太合适，太久了

[shakoon]

看你招的是什么级别的人。如果是高级的，这玩意 10 分钟还完不成那赶紧撵走吧，简历基本是吹牛的。中级建议 20 分钟，初级建议 35 分钟。我觉得这个题并不难啊，楼上怎么会有人觉得需要两小时的？

[cnxiaowen]

@shakoon 读题目一个半小时

[heiheidewo]

题目太长了，是我就直接走人了

[SergeGao]

不懂 sql 的前端表示这题看起来好像是入门级别的吧...

[richard1122]

看起来不难啊，不过 score 字段好像没用处?
这里找“每日最佳”是提交数量最多，并且 0 分也算有效提交

[pigzzz]

外企公司吗

[fishCatcher]

题目本质不难，还是弄短一点适合面试

[rioshikelong121]

题干太长了 直接走人。

[newtype0092]

@SergeGao @richard1122 @fishCatcher

[newtype0092]

sorry 点错发出去了。。。

每日最多提交这个条件很简单。
前面要求找出每天的符合"从开始到今日每天都有过提交"的人数，这个不知道大家什么思路？有没有什么优雅的办法不用复杂连表能解出来的？

[newtype0092]

@shakoon 大佬能提供下思路么？

[sunbeams001]

说下个人思路
先按照 hackerid+日期 分组，对结果集再按照 hackerid 分组，限制聚合计数为 15，即可得到每天都提交过的 hackerid
求更优雅的解法

[sgissb1]

对我来说给多少时间都一样。原因有两个：
1，英语不好，无法准确理解题目含义
2，考察的知识点不会。（主要不是做这块细分工作的）


但最致命的，还是人类语言障碍问题。

[shakoon]

@newtype0092 #18 group by hacker_id 得出提交提交次数，最大日期减最小日期得出天数，次数小于天数的那肯定就是某天没提交，不符合条件

[newtype0092]

@shakoon 这些逻辑如果用程序或者存储过程写看起来很简单，主要是就是想问有什么 SQL 技巧可以简单的算出来，如果全是字查询临时表来回套那感觉这题出得不太行。

[519718366]

让我想起了大学选修 oracle 数据库，最后考试的时候，最后一道纯英文，心里暗骂了下老师 懒狗，肯定是从哪复制粘贴的。🤪

[lihongming]

@shakoon 10 分钟能完成确实是高级的，可以说是数据专家了。不过不是专门做数据工作的程序员，对 SQL 应该没那么熟练。这题粗看简单，真做起来会发现挺绕的，要反复 join 、子查询，思路得极其清晰才可能快速做完。

而且这题里还有坑（这也是考察点之一，考察对需求的准确理解），虽然例子已经很明显了，但内测还是很多人掉进去了

[Meltdown]

hackerrank 的原题啊

[levelworm]

@newtype0092 只能想到提取名字然后 join 最后筛选的笨办法。。。

[lihongming]

@Meltdown 没错，这个题就是从那儿 copy 来的。

真正考试的难度与此类似，但不会让你 Google 到原题的 :D

[dustinth]

不专门刷题的能在半个小时做出来的程序员素质不低了, 取名字那一步会出现并列第一名的情况, 规则没说按照什么逻辑来选择.

[lihongming]

@dustinth 并列第一的问题有说明


If more than one such hacker has a maximum number of submissions, print the lowest hacker_id.

[goodboy95]

其他的没啥难度，反正子表套子表，使劲套就完事了。
不过 total number of unique hackers 这个……拿出来单独写好写，和其他的数据一起查的话，感觉这 sql 要爆炸……

[goodboy95]

@dustinth 并列第一取 id 最小，好像题目和示例 1 已经说明了这点

[wercbas2020]

今天恰巧做了一下面试题，整整 5 页。连谈话沟通加做题两个小时才做完。到最后一页已经累的不行了。再加上办公室里的人在谈话。最后一道题已经让我大脑难以思考。
所以我觉得你不要考虑做面试题的时间长短。会做就是会做，不会做就是不会做。
只要考试的时候不手机上网查资料就可以。

[Meltdown]

看了答案，要是知道用子查询来计算列的话就简单，不知道的话就得不停 join 或者套子表