15 Days of Learning SQL

https://www.hackerrank.com/challenges/15-days-of-learning-sql

• 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 1 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.
• 매일 한 번 이상 제출한 해커의 총 인원수와
  (= 현재 날짜를 포함하여 이전 날짜마다 적어도 한 번 이상 제출한 사람의 수는 얼마입니까?)
  각 날짜에서 가장 많은 제출을 한 해커의 hacker_id와 이름을 출력하는 것입니다.
  만약 가장 많은 제출을 한 해커가 둘 이상인 경우, hacker_id가 가장 낮은 해커를 출력해야 합니다.
  output col : date / total number of unique hackers / hacker id / name

SELECT submission_date,
-- total number of unique hackers
      (SELECT COUNT(DISTINCT hacker_id)
       FROM submissions AS sub2
       -- date match       
       WHERE sub2.submission_date = sub1.submission_date
       -- calculate the number of days from the start date
             AND (SELECT COUNT(DISTINCT submission_date)
                  FROM submissions AS sub3
                  WHERE sub3.hacker_id = sub2.hacker_id
                        AND sub3.submission_date < sub1.submission_date) = DATEDIFF(sub1.submission_date, '2016-03-01')),
-- hacker_id and name : maximum number of submissions each day                                   
      (SELECT hacker_id
       FROM submissions AS sub4
       WHERE sub4.submission_date = sub1.submission_date
       GROUP BY hacker_id
       ORDER BY COUNT(submission_id) DESC, hacker_id
       LIMIT 1) AS h_id,
        
      (SELECT name
       FROM hackers
       WHERE hacker_id = h_id)
        
FROM (SELECT DISTINCT submission_date FROM submissions) AS sub1

Lesson & Learned

처음 날짜에서는 DATEDIFF 가 0일 것이므로, 이전 제출의 수도 0이며 해당 날짜에 제출한 모든 사람을 나열합니다.