We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
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;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
15 Days of Learning SQL
You are viewing a single comment's thread. Return to all 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;