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_submission AS
(select submission_date,hacker_id,COUNT(*) as submissions
from Submissions where submission_date BETWEEN '2016-03-01' AND '2016-03-15'
GROUP BY submission_date,hacker_id),
Daily_top_hacker AS (select submission_date,hacker_id,RANK()OVER(PARTITION BY submission_date ORDER BY submissions DESC,hacker_id) AS rank FROM daily_submission)
SELECT dts.submission_date,
COUNT(DISTINCT dts.hacker_id) AS unq_hackers,
dth.hacker_id,
h.name
from daily_submission dts
INNER JOIN Daily_top_hacker dth
ON dts.submission_date = dth.submission_date AND dts.hacker_id=dth.hacker_id AND dth.rank=1
INNER JOIN Hackers h ON dth.hacker_id=h.hacker_id
GROUP BY dts.submission_date,dth.hacker_id,h.name
ORDER BY dts.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 →
whats wrong in this?
With daily_submission AS (select submission_date,hacker_id,COUNT(*) as submissions from Submissions where submission_date BETWEEN '2016-03-01' AND '2016-03-15' GROUP BY submission_date,hacker_id), Daily_top_hacker AS (select submission_date,hacker_id,RANK()OVER(PARTITION BY submission_date ORDER BY submissions DESC,hacker_id) AS rank FROM daily_submission) SELECT dts.submission_date, COUNT(DISTINCT dts.hacker_id) AS unq_hackers, dth.hacker_id, h.name from daily_submission dts INNER JOIN Daily_top_hacker dth ON dts.submission_date = dth.submission_date AND dts.hacker_id=dth.hacker_id AND dth.rank=1 INNER JOIN Hackers h ON dth.hacker_id=h.hacker_id GROUP BY dts.submission_date,dth.hacker_id,h.name ORDER BY dts.submission_date;