You are viewing a single comment's thread. Return to all comments →
with t as ( select submission_date, h.hacker_id, name, count( submission_id) as br, dense_rank() over (partition by h.hacker_id order by submission_date) as rbr, dense_rank() over (partition by submission_date order by count(*) desc,h.hacker_id ) as rk1 from hackers h inner join submissions s on h.hacker_id=s.hacker_id group by submission_date,h.hacker_id,name ), tt as ( select t.*, dense_rank() over (partition by submission_date order by br desc,hacker_id ) as rk, count( hacker_id) over (partition by submission_date) as broj from t where rbr=datediff(day,'2016-03-01',submission_date)+1 ) select t.submission_date,tt.broj,t.hacker_id,t.name from t inner join tt on t.submission_date=tt.submission_date where tt.rk=1 and t.rk1=1 order by 1
15 Days of Learning SQL
You are viewing a single comment's thread. Return to all comments →