15 Days of Learning SQL
15 Days of Learning SQL
+ 0 comments MS SQL Server
WITH cte1 AS ( SELECT DISTINCT submission_date FROM submissions), cte2 AS ( SELECT submission_date, ROW_NUMBER() OVER(ORDER BY submission_date) AS rank_date FROM cte1), cte3 AS ( SELECT DISTINCT submission_date, hacker_id FROM submissions), cte4 AS ( SELECT DISTINCT hacker_id, submission_date, ROW_NUMBER() OVER(PARTITION BY hacker_id ORDER BY submission_date) AS rank_hacker FROM cte3), cte5 AS ( SELECT DISTINCT cte2.submission_date, COUNT(cte4.hacker_id) OVER(PARTITION BY cte4.submission_date) AS cnt_id FROM cte2 JOIN cte4 ON cte2.submission_date = cte4.submission_date WHERE rank_date = rank_hacker), cte6 AS ( SELECT submission_date, hacker_id, COUNT(submission_id) AS cnt_sub FROM submissions GROUP BY submission_date, hacker_id), cte7 AS ( SELECT submission_date, hacker_id, ROW_NUMBER() OVER(PARTITION BY submission_date ORDER BY cnt_sub DESC, hacker_id) AS rank_hacker_daily FROM cte6) SELECT cte5.submission_date, cte5.cnt_id, cte7.hacker_id, h.name FROM cte5 JOIN cte7 ON cte5.submission_date = cte7.submission_date JOIN hackers h ON cte7.hacker_id = h.hacker_id WHERE cte7.rank_hacker_daily = 1;
+ 0 comments What's the issue in this Code ?
It's giving run time Error
with cte as (SELECT Submission_Date,hacker_id,count(hacker_id) as cnt, ROW_NUMBER() OVER (PARTITION BY Submission_Date ORDER BY cnt desc, hacker_id asc) AS row_num FROM Submissions GROUP by Submission_Date,hacker_id), t1 as (SELECT Submission_Date,count(DISTINCT(hacker_id))uniq FROM `Submissions` group BY Submission_Date), t2 as(select cte.Submission_Date,h.hacker_id,h.Name from cte join Hackers h on h.hacker_id=cte.hacker_id where cte.row_num=1) select t1.Submission_Date,t1.uniq,t2.hacker_id,t2.Name from t1 join t2 on t1.Submission_Date=t2.Submission_Date;
+ 0 comments **MS SQL **
with first_output as (Select submission_date,x.hacker_id,name from (Select submission_date,hacker_id,count(submission_id) cou, dense_rank() over(partition by submission_date order by count(submission_id) desc , hacker_id) r from submissions group by submission_date,hacker_id) x join hackers h on h.hacker_id = x.hacker_id where r = 1 ), cte as (Select distinct submission_date,hacker_id,dense_rank() over(order by submission_date) as rs from submissions), second_output as(Select c1.submission_date,count(hacker_id) users from cte c1 where hacker_id in (Select c2.hacker_id from cte c2 where c2.submission_Date < case when c1.submission_date = '2016-03-01' then dateadd(day,1,c1.submission_date) else c1.submission_date end group by c2.hacker_id having count(hacker_id) = (select max(rs) from cte c3 where c3.submission_date < case when c1.submission_date = '2016-03-01' then dateadd(day,1,c1.submission_date) else c1.submission_date end)) group by c1.submission_date ) Select fo.submission_date,users,hacker_id,name from first_output fo,second_output so where fo.submission_date = so.submission_Date order by fo.submission_date
+ 0 comments platform is broken , 1) MYSQL - unable to recognize even row_number() over (partition by ... order by ...) 2) I wasted an hour trying to see what is wrong , i tried copy pasting the top submission which got 50 points .. even that threw the same error .
what a useless website.
+ 0 comments --This worked fine for me -- with date_hacker as (select submission_date, hacker_id, count() as no_submissions from submissions s group by submission_date, hacker_id ), top_hacker as ( select a.submission_date, a.hacker_id, h.name from (select submission_date, hacker_id, rank() over (partition by submission_date order by no_submissions desc, hacker_id asc ) as rr from date_hacker) a inner join hackers h on a.hacker_id=h.hacker_id where a.rr=1 ), denserank as (select distinct submission_date, dense_rank() over (order by submission_date asc) as denserank from submissions), raw_d as ( select d1.submission_date,dr.denserank, d1.hacker_id from date_hacker d1 inner join date_hacker d2 on d1.submission_date>=d2.submission_date and d1.hacker_id=d2.hacker_id inner join denserank dr on d1.submission_date=dr.submission_date group by d1.submission_date,dr.denserank, d1.hacker_id having dr.denserank=count() ) select t.submission_date, coalesce(r.unique_hackers,0), t.hacker_id, t.name from top_hacker t left outer join (select submission_date, count(*) as unique_hackers from raw_d group by submission_date) r on t.submission_date=r.submission_date order by t.submission_date asc
Sort 835 Discussions, By:
Please Login in order to post a comment