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
An unexpected error occurred. Please try reloading the page. If problem persists, please contact support@hackerrank.com
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;