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.
-- select s.submission_date, s.submission_id, s.hacker_id
-- from Submissions s
with cte1 as (
select submission_date,count(distinct hacker_id) unique_hacker_ids
from (
select submission_date,hacker_id,
dense_rank() over(order by submission_date) date_r,
dense_rank() over(partition by hacker_id order by submission_date) hacker_r
from Submissions
)a where date_r=hacker_r
group by submission_date
),
cte2 as (
select submission_date, hacker_id
from (
select submission_date,hacker_id,
row_number() over(partition by submission_date order by count(hacker_id) desc, hacker_id) hacker_r
from Submissions
group by submission_date,hacker_id
)a
where 1=hacker_r
)
select cte1.submission_date,unique_hacker_ids,cte2.hacker_id,h.name
from cte1
join cte2 on cte1.submission_date=cte2.submission_date
join Hackers h on h.hacker_id=cte2.hacker_id
order by 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 →
-- select h.hacker_id,h.name -- from Hackers h
-- select s.submission_date, s.submission_id, s.hacker_id -- from Submissions s
with cte1 as ( select submission_date,count(distinct hacker_id) unique_hacker_ids from ( select submission_date,hacker_id, dense_rank() over(order by submission_date) date_r, dense_rank() over(partition by hacker_id order by submission_date) hacker_r from Submissions )a where date_r=hacker_r group by submission_date ), cte2 as ( select submission_date, hacker_id from ( select submission_date,hacker_id, row_number() over(partition by submission_date order by count(hacker_id) desc, hacker_id) hacker_r from Submissions group by submission_date,hacker_id )a where 1=hacker_r ) select cte1.submission_date,unique_hacker_ids,cte2.hacker_id,h.name from cte1 join cte2 on cte1.submission_date=cte2.submission_date join Hackers h on h.hacker_id=cte2.hacker_id order by submission_date;