We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
15 Days of Learning SQL
15 Days of Learning SQL
Sort by
recency
|
1008 Discussions
|
Please Login in order to post a comment
-- First CTE: This gives a cummulative sum of hackers across submission dates is calculated but only post the removal of duplicates from the submissions data as one user could have multiple submissions per day -- Second CTE: This gives details of those users with max submissions each day; WITH daily_submission_dtl AS ( select submission_date,COUNT() daily_submission_users from ( select submission_date,hacker_id, DENSE_RANK() OVER(ORDER BY submission_date ASC) date_rowno, COUNT() OVER(PARTITION BY hacker_id ORDER BY submission_date ASC) logins_till_day from ( select DISTINCT submission_date,hacker_id from Submissions ) A ) B
where date_rowno = logins_till_day group by submission_date ), daily_max_submission AS ( select submission_date,hacker_id,name from ( select submission_date,s.hacker_id,name, DENSE_RANK() OVER(PARTITION BY submission_date ORDER BY COUNT(*) DESC,s.hacker_id ASC) max_sub_rnk from Submissions s INNER JOIN Hackers h ON h.hacker_id = s.hacker_id group by submission_date,s.hacker_id,name ) A where max_sub_rnk = 1 ) select s1.submission_date,daily_submission_users,s2.hacker_id,name from daily_submission_dtl s1 INNER JOIN daily_max_submission s2 ON s1.submission_date = s2.submission_date order by s1.submission_date
MSSQL
MS SQL:
MS SQL Server