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.
SELECT
streaks.submission_date,
streaks.active_hackers_count,
top_h.hacker_id,
h.name
FROM
(
SELECT
s1.submission_date,
COUNT(DISTINCT s1.hacker_id) AS active_hackers_count
FROM
Submissions AS s1
WHERE
(
SELECT COUNT(DISTINCT s2.submission_date)
FROM Submissions AS s2
WHERE s2.hacker_id = s1.hacker_id AND s2.submission_date < s1.submission_date
) = DATEDIFF(s1.submission_date, '2016-03-01')
GROUP BY
s1.submission_date
) AS streaks
JOIN
(
SELECT
Ties.submission_date,
MIN(Ties.hacker_id) AS hacker_id
FROM
(
SELECT
dc.submission_date,
dc.hacker_id
FROM
(
SELECT submission_date, hacker_id, COUNT(1) AS subs_count
FROM Submissions
GROUP BY submission_date, hacker_id
) AS dc
JOIN
(
SELECT submission_date, MAX(subs_count) AS max_s
FROM (
SELECT submission_date, hacker_id, COUNT(1) AS subs_count
FROM Submissions
GROUP BY submission_date, hacker_id
) AS dc2
GROUP BY submission_date
) AS mc ON dc.submission_date = mc.submission_date AND dc.subs_count = mc.max_s
) AS Ties
GROUP BY
Ties.submission_date
) AS top_h ON streaks.submission_date = top_h.submission_date
JOIN
Hackers h ON top_h.hacker_id = h.hacker_id
ORDER BY
streaks.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 →
SELECT streaks.submission_date, streaks.active_hackers_count, top_h.hacker_id, h.name FROM ( SELECT s1.submission_date, COUNT(DISTINCT s1.hacker_id) AS active_hackers_count FROM Submissions AS s1 WHERE ( SELECT COUNT(DISTINCT s2.submission_date) FROM Submissions AS s2 WHERE s2.hacker_id = s1.hacker_id AND s2.submission_date < s1.submission_date ) = DATEDIFF(s1.submission_date, '2016-03-01') GROUP BY s1.submission_date ) AS streaks JOIN ( SELECT Ties.submission_date, MIN(Ties.hacker_id) AS hacker_id FROM ( SELECT dc.submission_date, dc.hacker_id FROM ( SELECT submission_date, hacker_id, COUNT(1) AS subs_count FROM Submissions GROUP BY submission_date, hacker_id ) AS dc JOIN ( SELECT submission_date, MAX(subs_count) AS max_s FROM ( SELECT submission_date, hacker_id, COUNT(1) AS subs_count FROM Submissions GROUP BY submission_date, hacker_id ) AS dc2 GROUP BY submission_date ) AS mc ON dc.submission_date = mc.submission_date AND dc.subs_count = mc.max_s ) AS Ties GROUP BY Ties.submission_date ) AS top_h ON streaks.submission_date = top_h.submission_date JOIN Hackers h ON top_h.hacker_id = h.hacker_id ORDER BY streaks.submission_date;