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.
This problem is horribly worded, but the explanation does make it clear that it's asking for two pieces of information:
1) how many hackers consecutively submistted for each day
2) the hacker that has most submits each day (they don't need to submit consecutively)
WITH p1 AS (
SELECT
submission_date,
COUNT(DISTINCT hacker_id) AS cnt
FROM (
SELECT
submission_date,
hacker_id,
DENSE_RANK() OVER (PARTITION BY hacker_id ORDER BY submission_date ASC) AS sub_day
FROM submissions
) s1
WHERE sub_day = DAY(submission_date)
GROUP BY submission_date
),
p2 AS (
SELECT
s3.submission_date,
s3.hacker_id,
h.name
FROM (
SELECT
submission_date,
hacker_id,
RANK() OVER (PARTITION BY submission_date ORDER BY cnt DESC, hacker_id ASC) AS rnk
FROM (
SELECT
submission_date,
hacker_id,
COUNT(*) AS cnt
FROM submissions
GROUP BY submission_date, hacker_id
) s2
) s3
INNER JOIN hackers h ON s3.hacker_id = h.hacker_id
WHERE s3.rnk = 1
)
SELECT
p1.submission_date,
p1.cnt,
p2.hacker_id,
p2.name
FROM p1
INNER JOIN p2 ON p1.submission_date = p2.submission_date
ORDER BY p1.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 →
This problem is horribly worded, but the explanation does make it clear that it's asking for two pieces of information: 1) how many hackers consecutively submistted for each day 2) the hacker that has most submits each day (they don't need to submit consecutively)
WITH p1 AS ( SELECT submission_date, COUNT(DISTINCT hacker_id) AS cnt FROM ( SELECT submission_date, hacker_id, DENSE_RANK() OVER (PARTITION BY hacker_id ORDER BY submission_date ASC) AS sub_day FROM submissions ) s1 WHERE sub_day = DAY(submission_date) GROUP BY submission_date ),
p2 AS ( SELECT s3.submission_date, s3.hacker_id, h.name FROM ( SELECT submission_date, hacker_id, RANK() OVER (PARTITION BY submission_date ORDER BY cnt DESC, hacker_id ASC) AS rnk FROM ( SELECT submission_date, hacker_id, COUNT(*) AS cnt FROM submissions GROUP BY submission_date, hacker_id ) s2 ) s3 INNER JOIN hackers h ON s3.hacker_id = h.hacker_id WHERE s3.rnk = 1 )
SELECT p1.submission_date, p1.cnt, p2.hacker_id, p2.name FROM p1 INNER JOIN p2 ON p1.submission_date = p2.submission_date ORDER BY p1.submission_date;