这是一个创建于 1540 天前的主题，其中的信息可能已经有所发展或是发生改变。
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.
34 条回复 • 2020-09-12 18:44:18 +08:00
 |
1
lihongming OP 奇了个怪了，table 为何不能正确解析？
|
 |
2
msg7086 2020-09-05 04:54:55 +08:00
面程序员还是面数据相关岗位？
|
|
3
lihongming OP @msg7086 程序员，除了这个还有算法题，时间另计
|
 |
4
way2explore2 2020-09-05 06:22:44 +08:00
取决于什么条件。
如果是现场做，最多给两个小时。 如果带回家，我会周一发，然后让周五交。 |
|
5
msg7086 2020-09-05 07:00:57 +08:00
|
|
6
lihongming OP |
 |
7
sealingpp 2020-09-05 08:25:03 +08:00
这全英文的,薪资范围多少,针对 什么级别的
|
|
8
lihongming OP @way2explore2 两个小时……那看来现场考不太合适，太久了
|
 |
9
shakoon 2020-09-05 08:46:32 +08:00
看你招的是什么级别的人。如果是高级的，这玩意 10 分钟还完不成那赶紧撵走吧，简历基本是吹牛的。中级建议 20 分钟，初级建议 35 分钟。我觉得这个题并不难啊，楼上怎么会有人觉得需要两小时的？
|
 |
11
heiheidewo 2020-09-05 09:26:48 +08:00
题目太长了，是我就直接走人了
|
 |
12
SergeGao 2020-09-05 09:30:59 +08:00
不懂 sql 的前端表示这题看起来好像是入门级别的吧...
|
 |
13
richard1122 2020-09-05 10:02:22 +08:00
看起来不难啊，不过 score 字段好像没用处?
这里找“每日最佳”是提交数量最多，并且 0 分也算有效提交 |
 |
14
pigzzz 2020-09-05 10:26:12 +08:00
外企公司吗
|
 |
15
fishCatcher 2020-09-05 10:55:47 +08:00 via iPhone
题目本质不难，还是弄短一点适合面试
|
 |
16
rioshikelong121 2020-09-05 11:20:20 +08:00  1
题干太长了 直接走人。
|
 |
17
newtype0092 2020-09-05 12:15:46 +08:00
|
|
18
newtype0092 2020-09-05 12:19:22 +08:00
sorry 点错发出去了。。。
每日最多提交这个条件很简单。 前面要求找出每天的符合"从开始到今日每天都有过提交"的人数，这个不知道大家什么思路？有没有什么优雅的办法不用复杂连表能解出来的？ |
|
19
newtype0092 2020-09-05 12:21:18 +08:00
@shakoon 大佬能提供下思路么？
|
 |
20
sunbeams001 2020-09-05 13:27:03 +08:00
说下个人思路
先按照 hackerid+日期 分组，对结果集再按照 hackerid 分组，限制聚合计数为 15，即可得到每天都提交过的 hackerid 求更优雅的解法 |
 |
21
sgissb1 2020-09-05 14:24:15 +08:00
对我来说给多少时间都一样。原因有两个：
1，英语不好，无法准确理解题目含义 2，考察的知识点不会。（主要不是做这块细分工作的） 但最致命的，还是人类语言障碍问题。 |
|
22
shakoon 2020-09-05 14:39:35 +08:00
@newtype0092 #18 group by hacker_id 得出提交提交次数，最大日期减最小日期得出天数，次数小于天数的那肯定就是某天没提交，不符合条件
|
|
23
newtype0092 2020-09-05 15:18:23 +08:00
@shakoon 这些逻辑如果用程序或者存储过程写看起来很简单，主要是就是想问有什么 SQL 技巧可以简单的算出来，如果全是字查询临时表来回套那感觉这题出得不太行。
|
 |
24
519718366 2020-09-05 16:47:53 +08:00 via iPhone 1
让我想起了大学选修 oracle 数据库，最后考试的时候，最后一道纯英文，心里暗骂了下老师 懒狗，肯定是从哪复制粘贴的。🤪
|
|
25
lihongming OP @shakoon 10 分钟能完成确实是高级的，可以说是数据专家了。不过不是专门做数据工作的程序员，对 SQL 应该没那么熟练。这题粗看简单，真做起来会发现挺绕的，要反复 join 、子查询，思路得极其清晰才可能快速做完。
而且这题里还有坑（这也是考察点之一，考察对需求的准确理解），虽然例子已经很明显了，但内测还是很多人掉进去了 |
 |
26
Meltdown 2020-09-06 11:29:39 +08:00 via Android
hackerrank 的原题啊
|
 |
27
levelworm 2020-09-06 22:08:16 +08:00 via Android
@newtype0092 只能想到提取名字然后 join 最后筛选的笨办法。。。
|
|
28
lihongming OP |
 |
29
dustinth 2020-09-07 08:08:11 +08:00
不专门刷题的能在半个小时做出来的程序员素质不低了, 取名字那一步会出现并列第一名的情况, 规则没说按照什么逻辑来选择.
|
|
30
lihongming OP @dustinth 并列第一的问题有说明
If more than one such hacker has a maximum number of submissions, print the lowest hacker_id. |
 |
31
goodboy95 2020-09-07 10:45:33 +08:00
其他的没啥难度，反正子表套子表，使劲套就完事了。
不过 total number of unique hackers 这个……拿出来单独写好写，和其他的数据一起查的话，感觉这 sql 要爆炸…… |
 |
33
wercbas2020 2020-09-09 21:34:38 +08:00
今天恰巧做了一下面试题，整整 5 页。连谈话沟通加做题两个小时才做完。到最后一页已经累的不行了。再加上办公室里的人在谈话。最后一道题已经让我大脑难以思考。
所以我觉得你不要考虑做面试题的时间长短。会做就是会做，不会做就是不会做。 只要考试的时候不手机上网查资料就可以。 |
|
34
Meltdown 2020-09-12 18:44:18 +08:00 via Android
看了答案，要是知道用子查询来计算列的话就简单，不知道的话就得不停 join 或者套子表
|
Select Language