15 Days of Learning SQL

  • + 0 comments

    WITH DailySubmissions AS ( -- Step 1: Unique hackers who submitted on each submission date SELECT DISTINCT s.submission_date, s.hacker_id FROM Submissions s ), SubmissionDates AS ( -- Step 2: Get all submission dates SELECT DISTINCT submission_date FROM Submissions ), UniqueHackersList AS ( -- Step 3: Count cumulative unique hackers who submitted on current & previous days SELECT sd.submission_date, ds.hacker_id, COUNT(*) AS hackers, (sd.submission_date - DATE '2016-03-01' + 1) AS days_past FROM SubmissionDates sd LEFT JOIN DailySubmissions ds ON sd.submission_date >= ds.submission_date GROUP BY sd.submission_date, ds.hacker_id ), AggSubmissions AS ( -- Step 4: Find the hacker with the most submissions each day SELECT s.submission_date, s.hacker_id, h.name, COUNT(s.submission_id) AS submissions, ROW_NUMBER() OVER (PARTITION BY s.submission_date ORDER BY COUNT(s.submission_id) DESC, s.hacker_id ASC) AS subs_rank FROM Submissions s JOIN Hackers h ON s.hacker_id = h.hacker_id GROUP BY s.submission_date, s.hacker_id, h.name ), UniqueHackers AS ( -- Step 5: Find the count of unique hackers who submitted every day from 2016-03-01 to the given date SELECT submission_date, COUNT(hacker_id) AS Unique_Hackers FROM UniqueHackersList WHERE hackers = days_past GROUP BY submission_date ) -- Final Selection SELECT uh.submission_date, uh.Unique_Hackers, subs.hacker_id, subs.name FROM UniqueHackers uh LEFT JOIN ( SELECT submission_date, hacker_id, name FROM AggSubmissions WHERE subs_rank = 1 ) subs ON uh.submission_date = subs.submission_date ORDER BY uh.submission_date;