You are viewing a single comment's thread. Return to all comments →
SQL Server Version for this code.
select table2.submission_date, table2.Unique_Count, table1.hacker_id , hackers.name from hackers, (select * from (select submission_date, hacker_id ,row_number() over (partition by submission_date order by count desc,hacker_id asc) as rn from (select submission_date, hacker_id, count(*) as count from submissions group by submission_date,hacker_id having count(*) >= 1 --order by submission_date ) a ) b where rn=1 ) table1, ( SELECT submission_date, COUNT(DISTINCT hacker_id) Unique_Count from ( SELECT d.submission_date, v.hacker_id, COUNT(DISTINCT v.submission_date) cnt fROM (SELECT DISTINCT submission_date FROM Submissions --ORDER BY submission_date ) d, submissions v where v.submission_date <= d.submission_date GROUP BY d.submission_date, v.hacker_id ) e WHERE Convert(Varchar(Max),day(submission_date)) = cnt GROUP BY submission_date --ORDER BY submission_date ) table2 where hackers.hacker_id = table1.hacker_id and table1.submission_date = table2.submission_date order by table1.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 →
SQL Server Version for this code.