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.
-- get daily submissions per hacker
with cte as (
SELECT
submission_date,
hacker_id,
count(distinct submission_id) as submissions
FROM submissions
GROUP BY submission_date, hacker_id
)
-- get submission days per hacker and rank the hackers by submissions made each day in descending order
, cte2 as (
SELECT
submission_date,
hacker_id,
submissions,
count(submission_date) OVER (PARTITION BY hacker_id order by submission_date rows between unbounded preceding and current row) AS submission_days,
datediff(day, cast('2016-03-01' as date), submission_date) + 1 as days_elapsed,
rank() over (partition by submission_date order by submissions desc, hacker_id) as max_submissions_rank
FROM cte
)
-- get the number of users who have been submitting daily since the start of the challenge and the hackerid with the maximum submissions per day
, cte3 as (
select
submission_date,
count(distinct case when days_elapsed = submission_days then hacker_id end) as daily_submit_hacker_cnt,
max(case when max_submissions_rank = 1 then hacker_id end) as max_submissions_hacker_id
from cte2
group by submission_date
)
-- get the name of the hacker who has maximum submissions per day
select
submission_date,
daily_submit_hacker_cnt,
max_submissions_hacker_id,
name as max_submissions_hacker_name
from cte3
left join hackers
on cte3.max_submissions_hacker_id = hackers.hacker_id
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 →
-- Using MS SQL Server
-- get daily submissions per hacker with cte as ( SELECT submission_date, hacker_id, count(distinct submission_id) as submissions FROM submissions GROUP BY submission_date, hacker_id )
-- get submission days per hacker and rank the hackers by submissions made each day in descending order , cte2 as ( SELECT submission_date, hacker_id, submissions, count(submission_date) OVER (PARTITION BY hacker_id order by submission_date rows between unbounded preceding and current row) AS submission_days, datediff(day, cast('2016-03-01' as date), submission_date) + 1 as days_elapsed, rank() over (partition by submission_date order by submissions desc, hacker_id) as max_submissions_rank FROM cte )
-- get the number of users who have been submitting daily since the start of the challenge and the hackerid with the maximum submissions per day , cte3 as ( select submission_date, count(distinct case when days_elapsed = submission_days then hacker_id end) as daily_submit_hacker_cnt, max(case when max_submissions_rank = 1 then hacker_id end) as max_submissions_hacker_id from cte2 group by submission_date )
-- get the name of the hacker who has maximum submissions per day select submission_date, daily_submit_hacker_cnt, max_submissions_hacker_id, name as max_submissions_hacker_name from cte3 left join hackers on cte3.max_submissions_hacker_id = hackers.hacker_id order by submission_date;