Untitled

mail@pastecode.io avatar
unknown
sql
2 years ago
816 B
2
Indexable
Never
/* Get the submission date */
SELECT submission_date,
/* Get the count of the unique hacker id that submitted on a distinct date */
(SELECT count(distinct hacker_id) FROM Submissions AS sub1 WHERE sub1.submission_date =
sub.submission_date
AND (SELECT COUNT(DISTINCT sub2.submission_date) FROM Submissions AS sub2 WHERE
sub2.submission_date < sub.submission_date AND sub2.hacker_id = sub1.hacker_id) =
DATEDIFF(sub.SUBMISSION_DATE , '2016-03-01')),
/* counting the hackers for consecutive submissions */
(SELECT hacker_id from Submissions AS sub1 where sub1.submission_date =
sub.submission_date GROUP BY hacker_id ORDER BY COUNT(submission_id) desc, hacker_id
LIMIT 1) AS sub3,
(SELECT name from hackers where hacker_id = sub3)
FROM (SELECT DISTINCT submission_date FROM Submissions) AS sub
Group BY submission_date