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: Calculate the number of unique hackers for each submission date
SELECT
s.submission_date,
COUNT(DISTINCT s.hacker_id) AS unique_hackers
FROM submissions s
WHERE s.submission_date BETWEEN '2016-03-01' AND '2016-03-15'
GROUP BY s.submission_date
),
MaxSubmissions AS (
-- Step 2: Calculate the hacker with the maximum number of submissions for each date
SELECT
s.submission_date,
s.hacker_id,
COUNT(s.submission_id) AS submission_count
FROM submissions s
WHERE s.submission_date BETWEEN '2016-03-01' AND '2016-03-15'
GROUP BY s.submission_date, s.hacker_id
),
MaxHacker AS (
-- Step 3: For each date, find the hacker with the maximum submissions
SELECT
ms.submission_date,
ms.hacker_id,
ms.submission_count,
ROW_NUMBER() OVER (PARTITION BY ms.submission_date ORDER BY ms.submission_count DESC, ms.hacker_id ASC) AS rn
FROM MaxSubmissions ms
)
-- Step 4: Combine results
SELECT
ds.submission_date,
ds.unique_hackers,
h.hacker_id,
h.name
FROM DailySubmissions ds
JOIN MaxHacker mh ON ds.submission_date = mh.submission_date
JOIN hackers h ON mh.hacker_id = h.hacker_id
WHERE mh.rn = 1
ORDER BY ds.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: Calculate the number of unique hackers for each submission date SELECT s.submission_date, COUNT(DISTINCT s.hacker_id) AS unique_hackers FROM submissions s WHERE s.submission_date BETWEEN '2016-03-01' AND '2016-03-15' GROUP BY s.submission_date ), MaxSubmissions AS ( -- Step 2: Calculate the hacker with the maximum number of submissions for each date SELECT s.submission_date, s.hacker_id, COUNT(s.submission_id) AS submission_count FROM submissions s WHERE s.submission_date BETWEEN '2016-03-01' AND '2016-03-15' GROUP BY s.submission_date, s.hacker_id ), MaxHacker AS ( -- Step 3: For each date, find the hacker with the maximum submissions SELECT ms.submission_date, ms.hacker_id, ms.submission_count, ROW_NUMBER() OVER (PARTITION BY ms.submission_date ORDER BY ms.submission_count DESC, ms.hacker_id ASC) AS rn FROM MaxSubmissions ms ) -- Step 4: Combine results SELECT ds.submission_date, ds.unique_hackers, h.hacker_id, h.name FROM DailySubmissions ds JOIN MaxHacker mh ON ds.submission_date = mh.submission_date JOIN hackers h ON mh.hacker_id = h.hacker_id WHERE mh.rn = 1 ORDER BY ds.submission_date;