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.
MS SQL SERVER:
with hacker_submission_per_date as (
select s.submission_date, s.hacker_id, h.name, count(distinct s.submission_id) as num_submissions
from submissions s
join hackers h on h.hacker_id = s.hacker_id
group by s.submission_date, s.hacker_id, h.name
having count(distinct s.submission_id) > 0
-- order by s.submission_date, s.hacker_id, h.name
)
, max_submission_per_date as (
select submission_date, hacker_id, name, num_submissions, row_number() over(partition by submission_date order by num_submissions desc, hacker_id) as rnk, row_number() over(partition by hacker_id order by submission_date ) as rnk_hacker,
dense_rank() over(order by submission_date) as date_rnk
from hacker_submission_per_date
)
, hackers as (
select submission_date, count(distinct hacker_id) cnt_unique_hackers
from max_submission_per_date
where date_rnk = rnk_hacker -- if date_rnk = rnk_hacker => this hacker participated all the date before this date
group by submission_date
)
select h.submission_date, h.cnt_unique_hackers, m.hacker_id, m.name --, m.num_submissions
from hackers h
join max_submission_per_date m on h.submission_date = m.submission_date
where rnk = 1
order by h.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 →
MS SQL SERVER: with hacker_submission_per_date as ( select s.submission_date, s.hacker_id, h.name, count(distinct s.submission_id) as num_submissions from submissions s join hackers h on h.hacker_id = s.hacker_id group by s.submission_date, s.hacker_id, h.name having count(distinct s.submission_id) > 0 -- order by s.submission_date, s.hacker_id, h.name )
, max_submission_per_date as ( select submission_date, hacker_id, name, num_submissions, row_number() over(partition by submission_date order by num_submissions desc, hacker_id) as rnk, row_number() over(partition by hacker_id order by submission_date ) as rnk_hacker, dense_rank() over(order by submission_date) as date_rnk from hacker_submission_per_date ) , hackers as ( select submission_date, count(distinct hacker_id) cnt_unique_hackers from max_submission_per_date
where date_rnk = rnk_hacker -- if date_rnk = rnk_hacker => this hacker participated all the date before this date group by submission_date )
select h.submission_date, h.cnt_unique_hackers, m.hacker_id, m.name --, m.num_submissions from hackers h join max_submission_per_date m on h.submission_date = m.submission_date where rnk = 1 order by h.submission_date