You are viewing a single comment's thread. Return to all comments →
WITH total_ranking AS ( SELECT submission_date, t1.hacker_id, t2.name, RANK() OVER (PARTITION BY submission_date ORDER BY score DESC, hacker_id ASC) AS ranking FROM Submissions t1 LEFT JOIN Hackers t2 ON t1.hacker_id = t2.hacker_id WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15' ), highest AS ( SELECT * FROM total_ranking WHERE ranking = 1 ), total_hackers AS ( SELECT submission_date, COUNT(DISTINCT hacker_id) AS counting FROM Submissions WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15' GROUP BY submission_date ) SELECT highest.submission_date, total_hackers.counting, highest.hacker_id, highest.name FROM highest JOIN total_hackers ON highest.submission_date = total_hackers.submission_date ORDER BY highest.submission_date;
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 →