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.
Correct working solution broken down biy by bit; \n
with daily_count as
(select submission_date,hacker_id,count() as num_sub
from submissions
where submission_date between to_date('March 01, 2016','month dd, yyyy') and to_date('March 15, 2016','month dd, yyyy')
group by submission_date,hacker_id),
daily_user as
(select submission_date,hacker_id,to_number(to_char(submission_date, 'dd')) as day,row_number() over (partition by hacker_id order by submission_date) as rn
from daily_count),
daily_user_count as
(select submission_date,count() as user_count
from daily_user
where day=rn
group by submission_date),
max_user as
(select submission_date,hacker_id,rank() over (partition by submission_date order by num_sub desc,hacker_id asc) as rank
from daily_count),
daily_max_user as
(select max_user.submission_date,hackers.hacker_id,hackers.name
from max_user
join hackers on max_user.hacker_id=hackers.hacker_id
where rank=1)
select daily_user_count.submission_date,daily_user_count.user_count,daily_max_user.hacker_id,daily_max_user.name
from daily_user_count
join daily_max_user on daily_user_count.submission_date=daily_max_user.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 →
Correct working solution broken down biy by bit; \n with daily_count as (select submission_date,hacker_id,count() as num_sub from submissions where submission_date between to_date('March 01, 2016','month dd, yyyy') and to_date('March 15, 2016','month dd, yyyy') group by submission_date,hacker_id), daily_user as (select submission_date,hacker_id,to_number(to_char(submission_date, 'dd')) as day,row_number() over (partition by hacker_id order by submission_date) as rn from daily_count), daily_user_count as (select submission_date,count() as user_count from daily_user where day=rn group by submission_date), max_user as (select submission_date,hacker_id,rank() over (partition by submission_date order by num_sub desc,hacker_id asc) as rank from daily_count), daily_max_user as (select max_user.submission_date,hackers.hacker_id,hackers.name from max_user join hackers on max_user.hacker_id=hackers.hacker_id where rank=1) select daily_user_count.submission_date,daily_user_count.user_count,daily_max_user.hacker_id,daily_max_user.name from daily_user_count join daily_max_user on daily_user_count.submission_date=daily_max_user.submission_date;