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.
-- 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
Cookie support is required to access HackerRank
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 →
-- 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