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.
select sup.*, h.name
from Hackers h, (
select s.submission_date, count(distinct s.hacker_id) as counter,
(select s4.hacker_id
from Submissions s4
where s4.submission_date = s.submission_date
group by s4.submission_date, s4.hacker_id
order by count(s4.submission_id) desc, s4.hacker_id asc
limit 1
) as hacker
from Submissions s
where s.hacker_id in (
select s2.hacker_id
from Submissions s2
where s2.submission_date <= s.submission_date
and datediff(s.submission_date, '2016-03-01')+1 = (
select count(distinct s3.submission_date)
from Submissions s3
where s3.hacker_id = s2.hacker_id
and s3.submission_date <= s.submission_date
group by s3.hacker_id
)
group by s2.hacker_id
)
group by s.submission_date
) sup
where sup.hacker = h.hacker_id
order by sup.submission_date asc
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 →
select sup.*, h.name from Hackers h, ( select s.submission_date, count(distinct s.hacker_id) as counter, (select s4.hacker_id from Submissions s4 where s4.submission_date = s.submission_date group by s4.submission_date, s4.hacker_id order by count(s4.submission_id) desc, s4.hacker_id asc limit 1 ) as hacker from Submissions s where s.hacker_id in ( select s2.hacker_id from Submissions s2 where s2.submission_date <= s.submission_date and datediff(s.submission_date, '2016-03-01')+1 = ( select count(distinct s3.submission_date) from Submissions s3 where s3.hacker_id = s2.hacker_id and s3.submission_date <= s.submission_date group by s3.hacker_id ) group by s2.hacker_id ) group by s.submission_date ) sup where sup.hacker = h.hacker_id order by sup.submission_date asc