You are viewing a single comment's thread. Return to all comments →
DECLARE @sDate date; DECLARE @dayCount INT; SET @sDate = '2016-03-01'; SET @dayCount = 0; CREATE TABLE tht ( hacker_id INT, submission_date date ) INSERT INTO tht SELECT distinct hacker_id, submission_date FROM Submissions WHERE Submission_date = @sDate; WHILE @dayCount < 15 BEGIN INSERT INTO tht SELECT distinct hacker_id, submission_date FROM Submissions WHERE Submission_date = DATEADD(day, @dayCount+1 , @sDate) AND hacker_id IN (SELECT distinct hacker_id FROM tht where submission_date = DATEADD(day, @dayCount, @sDate)); SELECT TOP 1 t1.sdt, t2.sc, t1.hacker_id, t1.name FROM (SELECT DATEADD(day, @dayCount, @sDate) sdt, count(s.hacker_id) hc, s.hacker_id, h.name, s.submission_date FROM Submissions s INNER JOIN Hackers h ON s.hacker_id = h.hacker_id GROUP BY s.hacker_id, h.name, s.submission_date ) t1 INNER JOIN (SELECT count(distinct hacker_id) sc, submission_date from tht WHERE submission_date = DATEADD(day, @dayCount, @sDate) group by submission_date) t2 ON t1.submission_date = t2.submission_date order by t1.hc desc, t1.hacker_id asc; SET @dayCount = @dayCount + 1 END
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 →