You are viewing a single comment's thread. Return to all comments →
declare @subdate date = '2016-03-01'; declare @dCount int = 1; create table demo ( hacker_id int, sub_date date ); insert into demo select hacker_id,submission_date from Submissions where submission_date=@subdate; select a.sub_date,a.h_c,b.hacker_id,b.name from ( ( select sub_date,count(distinct hacker_id) h_c from demo group by sub_date ) a inner join ( select top 1 sub_date,count(demo.hacker_id) d_c,demo.hacker_id,Hackers.name from demo inner join Hackers on demo.hacker_id=Hackers.hacker_id group by sub_date,demo.hacker_id,Hackers.name order by d_c desc,demo.hacker_id asc ) b on a.sub_date=b.sub_date ); while @dCount <= 15 begin set @subdate = dateadd(d,1,@subdate); delete from demo where hacker_id in ( select distinct hacker_id from demo except select distinct hacker_id from Submissions where submission_date = @subdate ); insert into demo select hacker_id,submission_date from Submissions where submission_date=@subdate and hacker_id in (select distinct hacker_id from demo); select a.sub_date,a.h_c,b.hacker_id,b.name from ( ( select sub_date,count(distinct hacker_id) h_c from demo where sub_date=@subdate group by sub_date ) a inner join ( select top 1 submission_date,count(Submissions.hacker_id) d_c,Submissions.hacker_id,Hackers.name from Submissions inner join Hackers on Submissions.hacker_id=Hackers.hacker_id where Submissions.submission_date = @subdate group by submission_date,Submissions.hacker_id,Hackers.name order by d_c desc,Submissions.hacker_id asc ) b on a.sub_date=b.submission_date ); set @dCount = @dCount+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 →