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
ds.submission_date,
(SELECT COUNT(DISTINCT hacker_id)
FROM Submissions d
WHERE d.submission_date = ds.submission_date
AND d.hacker_id IN (
SELECT hacker_id
FROM (
SELECT DISTINCT hacker_id, submission_date
FROM Submissions
WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15'
GROUP BY hacker_id, submission_date
) AS unique_hackers
)
) AS unique_hackers,
msh.hacker_id,
h.name
FROM
(SELECT DISTINCT submission_date
FROM Submissions
WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15'
) ds
LEFT JOIN (
SELECT
submission_date,
hacker_id,
COUNT(submission_id) AS submission_count,
RANK() OVER (PARTITION BY submission_date ORDER BY COUNT(submission_id) DESC, hacker_id ASC) AS rank
FROM Submissions
WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15'
GROUP BY submission_date, hacker_id
) AS msh
ON ds.submission_date = msh.submission_date AND msh.rank = 1
LEFT JOIN Hackers h
ON msh.hacker_id = h.hacker_id
ORDER BY ds.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 ds.submission_date, (SELECT COUNT(DISTINCT hacker_id) FROM Submissions d WHERE d.submission_date = ds.submission_date AND d.hacker_id IN ( SELECT hacker_id FROM ( SELECT DISTINCT hacker_id, submission_date FROM Submissions WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15' GROUP BY hacker_id, submission_date ) AS unique_hackers ) ) AS unique_hackers, msh.hacker_id, h.name FROM (SELECT DISTINCT submission_date FROM Submissions WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15' ) ds LEFT JOIN ( SELECT submission_date, hacker_id, COUNT(submission_id) AS submission_count, RANK() OVER (PARTITION BY submission_date ORDER BY COUNT(submission_id) DESC, hacker_id ASC) AS rank FROM Submissions WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15' GROUP BY submission_date, hacker_id ) AS msh ON ds.submission_date = msh.submission_date AND msh.rank = 1 LEFT JOIN Hackers h ON msh.hacker_id = h.hacker_id ORDER BY ds.submission_date;