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.
Hi, can anyone check what is wrong with the query? The cte1 and cte3 looks fine and able to find the max submission with min hacker id.
with cte1 as(
--find number of hacker submission per day
select distinct submission_date, count(distinct hacker_id) as "submission_count"
from submissions
group by submission_date),
cte2 as (
--count submission per hacker in each day and assign rank order by hacker id
select distinct submission_date, hacker_id,count(hacker_id) as"submission_count_per_day",row_number() over (partition by submission_date,count(hacker_id) order by hacker_id) as RowNum
from submissions
group by submission_date,hacker_id
),
cte3 as(
--select max submission in each day with min hacker id
select submission_date,max(submission_count_per_day) as "max_submission"
from cte2
where RowNum=1
group by submission_date)
select distinct a.submission_date, a.submission_count, c.hacker_id,d.name
from cte1 a
join cte3 b on a.submission_date=b.submission_date
join cte2 c on b.submission_date=c.submission_date and b.max_submission=c.submission_count_per_day and c.RowNum=1
join hackers d on c.hacker_id=d.hacker_id
order by a.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 →
Hi, can anyone check what is wrong with the query? The cte1 and cte3 looks fine and able to find the max submission with min hacker id.
with cte1 as( --find number of hacker submission per day select distinct submission_date, count(distinct hacker_id) as "submission_count" from submissions group by submission_date),
cte2 as ( --count submission per hacker in each day and assign rank order by hacker id select distinct submission_date, hacker_id,count(hacker_id) as"submission_count_per_day",row_number() over (partition by submission_date,count(hacker_id) order by hacker_id) as RowNum from submissions group by submission_date,hacker_id
), cte3 as( --select max submission in each day with min hacker id select submission_date,max(submission_count_per_day) as "max_submission" from cte2 where RowNum=1 group by submission_date)
select distinct a.submission_date, a.submission_count, c.hacker_id,d.name from cte1 a join cte3 b on a.submission_date=b.submission_date join cte2 c on b.submission_date=c.submission_date and b.max_submission=c.submission_count_per_day and c.RowNum=1 join hackers d on c.hacker_id=d.hacker_id order by a.submission_date