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 x AS
(SELECT
submission_date,
hacker_id,
COUNT(DISTINCT submission_id) AS subs
FROM Submissions
GROUP BY
submission_date,
hacker_id),
max_subs AS
(SELECT
submission_date,
x.hacker_id,
name,
ROW_NUMBER() OVER(PARTITION BY submission_date ORDER BY subs DESC, x.hacker_id) AS rank
FROM x
LEFT JOIN Hackers h
ON x.hacker_id = h.hacker_id),
streak_1 AS
(SELECT
hacker_id,
submission_date,
ROW_NUMBER() OVER(PARTITION BY hacker_id ORDER BY submission_date) AS consecutive_days
FROM x),
streak_2 AS
(SELECT
submission_date,
COUNT(DISTINCT hacker_id) AS consistent_hacker_count
FROM streak_1
WHERE consecutive_days = DATEDIFF(DAY, '2016-03-01', submission_date) + 1
GROUP BY
submission_date)
SELECT
ds.submission_date,
consistent_hacker_count,
ts.hacker_id,
ts.name
FROM (SELECT DISTINCT submission_date FROM Submissions) ds
LEFT JOIN streak_2 ch
ON ds.submission_date = ch.submission_date
LEFT JOIN max_subs ts
ON ds.submission_date = ts.submission_date
AND ts.rank = 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 x AS (SELECT submission_date, hacker_id, COUNT(DISTINCT submission_id) AS subs FROM Submissions GROUP BY submission_date, hacker_id),
max_subs AS (SELECT submission_date, x.hacker_id, name, ROW_NUMBER() OVER(PARTITION BY submission_date ORDER BY subs DESC, x.hacker_id) AS rank FROM x LEFT JOIN Hackers h ON x.hacker_id = h.hacker_id),
streak_1 AS (SELECT hacker_id, submission_date, ROW_NUMBER() OVER(PARTITION BY hacker_id ORDER BY submission_date) AS consecutive_days FROM x),
streak_2 AS (SELECT submission_date, COUNT(DISTINCT hacker_id) AS consistent_hacker_count FROM streak_1 WHERE consecutive_days = DATEDIFF(DAY, '2016-03-01', submission_date) + 1 GROUP BY submission_date)
SELECT ds.submission_date, consistent_hacker_count, ts.hacker_id, ts.name FROM (SELECT DISTINCT submission_date FROM Submissions) ds LEFT JOIN streak_2 ch ON ds.submission_date = ch.submission_date LEFT JOIN max_subs ts ON ds.submission_date = ts.submission_date AND ts.rank = 1 ORDER BY ds.submission_date;