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.
with cte as(
select submission_date, hacker_id,
count(*) as no_of_submissions,
dense_rank() over(partition by submission_date order by submission_date) as day_number
from Submissions
group by submission_date, hacker_id),
cte2 as(
select ,
count() over(partition by hacker_id order by submission_date) as till_date_submission,
case when day_number = count(*) over(partition by hacker_id order by submission_date) then 1 else 0 end as unique_flag
from cte
),cte3 as(
select *,
sum(unique_flag) over(partition by submission_date) as unique_count,
row_number() over(partition by submission_date order by no_of_submissions desc, hacker_id) as rn
from cte2
)
select submission_date,unique_count,c.hacker_id,name
from cte3 c join Hackers h
on c.hacker_id = h.hacker_id
where rn = 1
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 →
What is wrong with the following?
with cte as( select submission_date, hacker_id, count(*) as no_of_submissions, dense_rank() over(partition by submission_date order by submission_date) as day_number from Submissions group by submission_date, hacker_id), cte2 as( select , count() over(partition by hacker_id order by submission_date) as till_date_submission, case when day_number = count(*) over(partition by hacker_id order by submission_date) then 1 else 0 end as unique_flag from cte ),cte3 as( select *, sum(unique_flag) over(partition by submission_date) as unique_count, row_number() over(partition by submission_date order by no_of_submissions desc, hacker_id) as rn from cte2 ) select submission_date,unique_count,c.hacker_id,name from cte3 c join Hackers h on c.hacker_id = h.hacker_id where rn = 1 order by submission_date;