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.
Here is my solution
this looks complex but it's easy to understand if you try..
try to divide question in parts and solve according to it..
select submission_date, number_of_unique_sub, hacker_id, name
from
(select *
from(
select submission_date, count(distinct hacker_id) as number_of_unique_sub
from(
select *, date_add(submission_date, interval -(rn) day) as date_rn
from(
select *, dense_rank() over(partition by hacker_id order by submission_date) as rn
from submissions) a
where date_add(submission_date, interval -(rn) day) = '2016-02-29') b
group by submission_date) c
JOIN(
select *
from
(select *, row_number() over(partition by submission__date order by num desc, hacker__id) as num2
from(
select submission_date as submission__date, hacker_id as hacker__id, count(1) as num
from submissions
group by submission__date, hacker__id) a
) b
where num2 = 1) k
ON c.submission_date = k.submission__date
JOIN hackers h
ON k.hacker__id = h.hacker_id) d
order by 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 →
Here is my solution this looks complex but it's easy to understand if you try..
try to divide question in parts and solve according to it..
select submission_date, number_of_unique_sub, hacker_id, name from (select * from( select submission_date, count(distinct hacker_id) as number_of_unique_sub from( select *, date_add(submission_date, interval -(rn) day) as date_rn from( select *, dense_rank() over(partition by hacker_id order by submission_date) as rn from submissions) a where date_add(submission_date, interval -(rn) day) = '2016-02-29') b group by submission_date) c
JOIN( select * from (select *, row_number() over(partition by submission__date order by num desc, hacker__id) as num2 from( select submission_date as submission__date, hacker_id as hacker__id, count(1) as num from submissions group by submission__date, hacker__id) a ) b where num2 = 1) k ON c.submission_date = k.submission__date JOIN hackers h ON k.hacker__id = h.hacker_id) d order by submission_date