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.
cte1 as
(
select hacker_id, submission_date, dense_rank() over (PARTITION BY hacker_id order by submission_date) as RN from submissions
),
cte2 as
(
select s.submission_date, s.hacker_id, h.name, row_number() over (partition by s.submission_date order by count(s.submission_id)desc, s.hacker_id asc) as Number from submissions as s join hackers as h on s.hacker_id = h.hacker_id group by s.submission_date, s.hacker_id,h.name
)
select a1.submission_date, total, hacker_id, name from
(select submission_date,count(distinct(hacker_id)) as Total from cte1 where rn = datepart(day, submission_date) group by submission_date)
as A1
join
(select submission_date, hacker_id, name from cte2 where number = 1 )
As A2
on A1.submission_date = A2.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 →
MS SQL solution I have used two cte in this query
with
cte1 as ( select hacker_id, submission_date, dense_rank() over (PARTITION BY hacker_id order by submission_date) as RN from submissions ),
cte2 as
( select s.submission_date, s.hacker_id, h.name, row_number() over (partition by s.submission_date order by count(s.submission_id)desc, s.hacker_id asc) as Number from submissions as s join hackers as h on s.hacker_id = h.hacker_id group by s.submission_date, s.hacker_id,h.name )
select a1.submission_date, total, hacker_id, name from
(select submission_date,count(distinct(hacker_id)) as Total from cte1 where rn = datepart(day, submission_date) group by submission_date)
as A1
join
(select submission_date, hacker_id, name from cte2 where number = 1 )
As A2
on A1.submission_date = A2.submission_date