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.
/*
Enter your query here.
Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
/
/
Enter your query here.
Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
/
with t1 as(
SELECT s.submission_date,s.hacker_id,h.name, COUNT(submission_id) as cnt from submissions s inner join hackers h on h.hacker_id=s.hacker_id group by s.hacker_id,h.name,s.submission_date
),
t2 as(
select submission_date, hacker_id, row_number() over(partition by hacker_id order by submission_date) rn, dense_rank() over(order by submission_date) dr from t1
),
t3 as(
select t2.submission_date,count() as cnt1 from t2 where
t2.rn=t2.dr
group by t2.submission_date
),
t4 as(
Select submission_date,hacker_id,name from (select submission_date,hacker_id,name, row_number() over(partition by submission_date order by cnt desc,hacker_id) rn from t1)as t where t.rn=1
)
select t3.submission_date, t3.cnt1,t4.hacker_id,t4.name from t3 inner join t4 on t3.submission_date=t4.submission_date order by t3.submission_date https://)
15 Days of Learning SQL
You are viewing a single comment's thread. Return to all comments →
/* Enter your query here. Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error. / / Enter your query here. Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error. / with t1 as( SELECT s.submission_date,s.hacker_id,h.name, COUNT(submission_id) as cnt from submissions s inner join hackers h on h.hacker_id=s.hacker_id group by s.hacker_id,h.name,s.submission_date ), t2 as( select submission_date, hacker_id, row_number() over(partition by hacker_id order by submission_date) rn, dense_rank() over(order by submission_date) dr from t1 ), t3 as( select t2.submission_date,count() as cnt1 from t2 where t2.rn=t2.dr group by t2.submission_date ), t4 as( Select submission_date,hacker_id,name from (select submission_date,hacker_id,name, row_number() over(partition by submission_date order by cnt desc,hacker_id) rn from t1)as t where t.rn=1 ) select t3.submission_date, t3.cnt1,t4.hacker_id,t4.name from t3 inner join t4 on t3.submission_date=t4.submission_date order by t3.submission_date
https://)