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 daily_count as
(
select s.submission_date,s.hacker_id,h.name,count(s.submission_id) as dail_cna
from submissions s left join hackers h on s.hacker_id=h.hacker_id
group by s.submission_date,s.hacker_id,h.name
),
max_daily as
(
select submission_date,hacker_id,name,
row_number() over(partition by submission_date order by dail_cna desc,hacker_id) as rna
from daily_count
),
fin as
(
select hacker_id,submission_date,
dense_rank() over(partition by hacker_id order by submission_date ) as sina
from submissions
group by hacker_id,submission_date
),
fina1 as
(
select submission_date,count(hacker_id) as bbc from fin where day(submission_date)=sina
group by submission_date
)
select distinct m.submission_date,f.bbc,m.hacker_id,m.name
from max_daily m left join fina1 f on m.submission_date=f.submission_date
where rna=1;
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 →
with daily_count as ( select s.submission_date,s.hacker_id,h.name,count(s.submission_id) as dail_cna from submissions s left join hackers h on s.hacker_id=h.hacker_id group by s.submission_date,s.hacker_id,h.name ), max_daily as ( select submission_date,hacker_id,name, row_number() over(partition by submission_date order by dail_cna desc,hacker_id) as rna from daily_count ), fin as ( select hacker_id,submission_date, dense_rank() over(partition by hacker_id order by submission_date ) as sina from submissions group by hacker_id,submission_date ), fina1 as ( select submission_date,count(hacker_id) as bbc from fin where day(submission_date)=sina group by submission_date ) select distinct m.submission_date,f.bbc,m.hacker_id,m.name from max_daily m left join fina1 f on m.submission_date=f.submission_date where rna=1;