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 one_sub AS (
SELECT hacker_id
FROM submissions
GROUP BY hacker_id
HAVING COUNT(DISTINCT submission_date)=15
),
active_hackers AS (
SELECT s.submission_date, COUNT(DISTINCT hacker_id) AS total_active
FROM submissions s
JOIN one_sub os
ON s.hacker_id=os.hacker_id
GROUP BY s.submission_date
),
hacker_daily_subs AS (
SELECT submission_date,
hacker_id,
COUNT(*) AS total_subs
FROM submissions
GROUP BY submission_date, hacker_id
),
ranked_subs AS (
SELECT *,
RANK() OVER (PARTITION BY submission_date ORDER BY total_subs DESC, hacker_id) AS rnk
FROM hacker_daily_subs)
SELECT
r.submission_date,
a.total_active,
r.hacker_id,
h.name
FROM ranked_subs r
JOIN hackers h ON r.hacker_id = h.hacker_id
JOIN active_hackers a ON r.submission_date = a.submission_date
WHERE r.rnk = 1
ORDER BY r.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 →
Can you help why this doesn't work?
WITH one_sub AS ( SELECT hacker_id FROM submissions GROUP BY hacker_id HAVING COUNT(DISTINCT submission_date)=15 ), active_hackers AS ( SELECT s.submission_date, COUNT(DISTINCT hacker_id) AS total_active FROM submissions s JOIN one_sub os ON s.hacker_id=os.hacker_id GROUP BY s.submission_date ), hacker_daily_subs AS ( SELECT submission_date, hacker_id, COUNT(*) AS total_subs FROM submissions GROUP BY submission_date, hacker_id ), ranked_subs AS ( SELECT *, RANK() OVER (PARTITION BY submission_date ORDER BY total_subs DESC, hacker_id) AS rnk FROM hacker_daily_subs)
SELECT r.submission_date, a.total_active, r.hacker_id, h.name FROM ranked_subs r JOIN hackers h ON r.hacker_id = h.hacker_id JOIN active_hackers a ON r.submission_date = a.submission_date WHERE r.rnk = 1 ORDER BY r.submission_date;