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 DateSeries AS (
-- Generate a list of dates from 2016-03-01 to 2016-03-15
SELECT CAST('2016-03-01' AS DATE) AS submission_date
UNION ALL
SELECT DATEADD(DAY, 1, submission_date)
FROM DateSeries
WHERE submission_date < '2016-03-15'
),
DateRowNumbers AS (
-- Assign a row number to each date
SELECT submission_date, ROW_NUMBER() OVER (ORDER BY submission_date) AS day
FROM DateSeries
),
HackerSubmissions AS (
-- Count the number of submissions per hacker per day
SELECT submission_date, hacker_id, COUNT(submission_id) AS count_sub
FROM submissions
GROUP BY submission_date, hacker_id
),
HackerRanks AS (
-- Assign a row number to each hacker to identify the hacker with the most submissions for each day
SELECT submission_date, hacker_id, ROW_NUMBER() OVER (PARTITION BY submission_date ORDER BY count_sub DESC, hacker_id) AS day_champ
FROM HackerSubmissions
),
DayChampions AS (
-- Select the hacker with the most submissions for each day
SELECT ds.submission_date, hr.hacker_id, h.name
FROM DateRowNumbers ds
LEFT JOIN HackerRanks hr ON ds.submission_date = hr.submission_date
LEFT JOIN hackers h ON hr.hacker_id = h.hacker_id
WHERE hr.day_champ = 1
),
HackerStreaks AS (
-- Generate a row number for each day in streak for each hacker
SELECT submission_date, hacker_id, count_sub,
ROW_NUMBER() OVER (PARTITION BY hacker_id ORDER BY submission_date) AS day_row
FROM HackerSubmissions
),
StreakDetails AS (
-- Join the day numbers with the hacker's submission data
SELECT ds.submission_date, hs.hacker_id, hs.count_sub, hs.day_row, ds.day
FROM DateRowNumbers ds
LEFT JOIN HackerStreaks hs ON ds.submission_date = hs.submission_date
WHERE ds.day = hs.day_row
),
TotalParticipants AS (
-- Count the number of hackers who participated on each day
SELECT submission_date, COUNT(hacker_id) AS total_participants_streak
FROM StreakDetails
GROUP BY submission_date
)
-- Final result: Join the total participants per day with the day champions
SELECT tp.submission_date, tp.total_participants_streak, dc.hacker_id, dc.name
FROM TotalParticipants tp
LEFT JOIN DayChampions dc ON tp.submission_date = dc.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 DateSeries AS ( -- Generate a list of dates from 2016-03-01 to 2016-03-15 SELECT CAST('2016-03-01' AS DATE) AS submission_date UNION ALL SELECT DATEADD(DAY, 1, submission_date) FROM DateSeries WHERE submission_date < '2016-03-15' ), DateRowNumbers AS ( -- Assign a row number to each date SELECT submission_date, ROW_NUMBER() OVER (ORDER BY submission_date) AS day FROM DateSeries ), HackerSubmissions AS ( -- Count the number of submissions per hacker per day SELECT submission_date, hacker_id, COUNT(submission_id) AS count_sub FROM submissions GROUP BY submission_date, hacker_id ), HackerRanks AS ( -- Assign a row number to each hacker to identify the hacker with the most submissions for each day SELECT submission_date, hacker_id, ROW_NUMBER() OVER (PARTITION BY submission_date ORDER BY count_sub DESC, hacker_id) AS day_champ FROM HackerSubmissions ), DayChampions AS ( -- Select the hacker with the most submissions for each day SELECT ds.submission_date, hr.hacker_id, h.name FROM DateRowNumbers ds LEFT JOIN HackerRanks hr ON ds.submission_date = hr.submission_date LEFT JOIN hackers h ON hr.hacker_id = h.hacker_id WHERE hr.day_champ = 1 ), HackerStreaks AS ( -- Generate a row number for each day in streak for each hacker SELECT submission_date, hacker_id, count_sub, ROW_NUMBER() OVER (PARTITION BY hacker_id ORDER BY submission_date) AS day_row FROM HackerSubmissions ), StreakDetails AS ( -- Join the day numbers with the hacker's submission data SELECT ds.submission_date, hs.hacker_id, hs.count_sub, hs.day_row, ds.day FROM DateRowNumbers ds LEFT JOIN HackerStreaks hs ON ds.submission_date = hs.submission_date WHERE ds.day = hs.day_row ), TotalParticipants AS ( -- Count the number of hackers who participated on each day SELECT submission_date, COUNT(hacker_id) AS total_participants_streak FROM StreakDetails GROUP BY submission_date ) -- Final result: Join the total participants per day with the day champions SELECT tp.submission_date, tp.total_participants_streak, dc.hacker_id, dc.name FROM TotalParticipants tp LEFT JOIN DayChampions dc ON tp.submission_date = dc.submission_date;