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 t1 AS (
SELECT submission_date, hacker_id, COUNT(submission_id) AS cnt
FROM Submissions
GROUP BY submission_date, hacker_id
),
t2 AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY submission_date ORDER BY cnt DESC, hacker_id) AS rn
FROM t1
),
t3 AS (
SELECT t2.submission_date, t2.hacker_id, b.name, t2.cnt
FROM t2
JOIN Hackers b ON t2.hacker_id = b.hacker_id
WHERE t2.rn = 1
),
t4 AS (
SELECT hacker_id, submission_date,
Dense_rank() OVER (PARTITION BY hacker_id ORDER BY submission_date) AS row_num
FROM Submissions
group by hacker_id, submission_date
),
t5 AS (
SELECT submission_date, count(hacker_id) as hcnt
FROM t4
where Datepart(Day, submission_date) = row_num
group by submission_date
)
SELECT t5.submission_date, hcnt, t3.hacker_id, t3.name FROM t5
join t3 on t5.submission_date = t3.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 t1 AS ( SELECT submission_date, hacker_id, COUNT(submission_id) AS cnt FROM Submissions GROUP BY submission_date, hacker_id ), t2 AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY submission_date ORDER BY cnt DESC, hacker_id) AS rn FROM t1 ), t3 AS ( SELECT t2.submission_date, t2.hacker_id, b.name, t2.cnt FROM t2 JOIN Hackers b ON t2.hacker_id = b.hacker_id WHERE t2.rn = 1 ), t4 AS ( SELECT hacker_id, submission_date, Dense_rank() OVER (PARTITION BY hacker_id ORDER BY submission_date) AS row_num FROM Submissions group by hacker_id, submission_date ), t5 AS ( SELECT submission_date, count(hacker_id) as hcnt FROM t4 where Datepart(Day, submission_date) = row_num group by submission_date )
SELECT t5.submission_date, hcnt, t3.hacker_id, t3.name FROM t5 join t3 on t5.submission_date = t3.submission_date
;