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 daily_submissions AS (
SELECT submission_date,
hacker_id,
COUNT(*) AS num_subs
FROM Submissions
GROUP BY submission_date, hacker_id
),
max_daily AS (
SELECT ds.submission_date,
ds.hacker_id,
ds.num_subs,
RANK() OVER (PARTITION BY ds.submission_date
ORDER BY ds.num_subs DESC, ds.hacker_id ASC) AS rnk
FROM daily_submissions ds
),
cumulative_hackers AS (
SELECT s.submission_date,
COUNT(DISTINCT s.hacker_id) AS total_hackers
FROM Submissions s
WHERE NOT EXISTS (
SELECT 1
FROM (
SELECT DISTINCT submission_date
FROM Submissions
) d
WHERE d.submission_date <= s.submission_date
AND NOT EXISTS (
SELECT 1
FROM Submissions s2
WHERE s2.hacker_id = s.hacker_id
AND s2.submission_date = d.submission_date
)
)
GROUP BY s.submission_date
)
SELECT ch.submission_date,
ch.total_hackers,
m.hacker_id,
h.name
FROM cumulative_hackers ch
JOIN max_daily m
ON ch.submission_date = m.submission_date
AND m.rnk = 1
JOIN Hackers h
ON m.hacker_id = h.hacker_id
ORDER BY ch.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 daily_submissions AS ( SELECT submission_date, hacker_id, COUNT(*) AS num_subs FROM Submissions GROUP BY submission_date, hacker_id ), max_daily AS ( SELECT ds.submission_date, ds.hacker_id, ds.num_subs, RANK() OVER (PARTITION BY ds.submission_date ORDER BY ds.num_subs DESC, ds.hacker_id ASC) AS rnk FROM daily_submissions ds ), cumulative_hackers AS ( SELECT s.submission_date, COUNT(DISTINCT s.hacker_id) AS total_hackers FROM Submissions s WHERE NOT EXISTS ( SELECT 1 FROM ( SELECT DISTINCT submission_date FROM Submissions ) d WHERE d.submission_date <= s.submission_date AND NOT EXISTS ( SELECT 1 FROM Submissions s2 WHERE s2.hacker_id = s.hacker_id AND s2.submission_date = d.submission_date ) ) GROUP BY s.submission_date ) SELECT ch.submission_date, ch.total_hackers, m.hacker_id, h.name FROM cumulative_hackers ch JOIN max_daily m ON ch.submission_date = m.submission_date AND m.rnk = 1 JOIN Hackers h ON m.hacker_id = h.hacker_id ORDER BY ch.submission_date;