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.
15 Days of Learning SQL
15 Days of Learning SQL
Sort by
recency
|
1001 Discussions
|
Please Login in order to post a comment
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
Solution in sql server with sub (hacker_id,name,score,submission_date) as( select a.hacker_id,b.name,count() as score,a.submission_date from submissions as a join hackers as b on a.hacker_id=b.hacker_id
group by a.hacker_id,a.submission_date,b.name), /----------------------------------------------------------------------------------------*/ maxi (submission_date,score) as(select distinct submission_date,max(score) from sub group by submission_date), minimax (hacker_id,submission_date,score) as(select min(a.hacker_id) ,b.submission_date,b.score from sub a join maxi b on b.submission_date=a.submission_date and b.score=a.score group by b.submission_date,b.score),
/----------------------------------------------------------------------------------------/ fechas(hacker_id,submission_date) as( select hacker_id,
convert(date, '2016-03-01') as submission_date from submissions as s where submission_date='2016-03-01' union all select s.hacker_id, dateadd(day,1,f.submission_date) from submissions s join fechas f on f.hacker_id=s.hacker_id where s.submission_date=dateadd(day,1,f.submission_date) and f.submission_date-----------------------------------------------------------------------------------------/ id(submission_date,cantidad) as( select submission_date,count(distinct hacker_id)from fechas
group by submission_date
) /-----------------------------------------------------------------------------------------/
select a.submission_date,id.cantidad, b.hacker_id,a.name from sub as a join minimax as b on a.submission_date=b.submission_date and a.hacker_id=b.hacker_id join id on id.submission_date=a.submission_date
order by a.submission_date;
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