15 days of learning SQL

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

15 Days of Learning SQL | HackerRank

해결 방법 : 서브 쿼리 JOIN

• 현재까지 매일 submission하고 있는 사람을 구하기 위한 JOIN 
  • (현재 날짜 - contest 첫째 날짜)가 현재 날짜 이전의 distinct submission 횟수와 같은지 판별했다.
• 각 날짜별 가장 많이 submission한 사람 구하는 JOIN : 코드 참고

(어려웠다.. 조만간 다시 풀어보자.)

SELECT s.submission_date, s_count.hacker_count, s_best.best_hacker, h.name
FROM submissions as s
JOIN ( -- 현재까지 매일 submission 하는 hackers
    SELECT submission_date, count(distinct hacker_id) as hacker_count
    FROM submissions as s1
    WHERE submission_date - DATE('2016-03-01') = (
        SELECT count(distinct submission_date)
        FROM submissions as s2
        WHERE s2.submission_date < s1.submission_date
        and s2.hacker_id = s1.hacker_id
        )
    GROUP BY submission_date
    ) as s_count on s.submission_date = s_count.submission_date
JOIN ( -- 각 날짜별 가장 많이 submission한 hacker 
    SELECT submission_date, hacker_id as best_hacker
    FROM submissions as s3
    WHERE hacker_id = (
        SELECT hacker_id
        FROM submissions as s4
        WHERE s4.submission_date = s3.submission_date
        GROUP BY hacker_id
        ORDER BY count(submission_id) desc, hacker_id limit 1
        )
    GROUP BY submission_date, hacker_id
    ) as s_best on s.submission_date = s_best.submission_date
JOIN hackers as h on s_best.best_hacker = h.hacker_id
GROUP BY s.submission_date, s_count.hacker_count, s_best.best_hacker, h.name
ORDER BY s.submission_date;