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.
with a as
(select submissionDate, hackerid , count() as submissiontimes from submissions
group by submissionDate, hackerid
),
b as
(select ,Rownumber()over(partition by submissionDate order by submissiontimes desc, hackerid) as seq
from a),
e as
(select ,day(submissionDate) as submissionDay,Denserank()over(partition by hackerid order by submissionDate) as seq
from submissions)
select c.submissionDate, f.num ,c.hackerid,d.name
from
(select from b
where seq=1
) c
left join
hackers d
on c.hackerid=d.hackerid
left join
(select submissionDate,count(distinct hackerid) as num from e
where submissionDay=seq
group by submissionDate) f
on c.submissionDate = f.submissionDate
order by submissionDate;
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 →
with a as (select submissionDate, hackerid , count() as submissiontimes from submissions group by submissionDate, hackerid ), b as (select ,Rownumber()over(partition by submissionDate order by submissiontimes desc, hackerid) as seq from a), e as (select ,day(submissionDate) as submissionDay,Denserank()over(partition by hackerid order by submissionDate) as seq from submissions) select c.submissionDate, f.num ,c.hackerid,d.name from (select from b where seq=1 ) c left join hackers d on c.hackerid=d.hackerid left join (select submissionDate,count(distinct hackerid) as num from e where submissionDay=seq group by submissionDate) f on c.submissionDate = f.submissionDate order by submissionDate;