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 cons_submission as (
select h.hacker_id,h.name,s.submission_date,
dense_rank() over (partition by submission_date,h.hacker_id order by submission_date asc) as cons_sub
from hacker h
inner join submissions s on h.hacker_id = s.hacker_id),
hacker_XV as (
select hacker_id,name
from cons_submission
group by hacker_id,name
having max(cons_sub) = 15),
hackers_fltrd as (
select cs.hacker_id,cs.name,cs.submission_date,
count(*) as submitted_count
from cons_submission cs
inner join hacker_XV hxv on cs.hacker_id = hxv.hacker_id
group by hacker_id,name,submission_date
),
cte as (
select hacker_id,name,submission_date,submitted_count,
row_number() over (partition by submission_date order by submitted_count desc, hacker_id asc ) as row_no
from hackers_fltrd
)
select submission_date,submitted_count,hacker_id,name
from cte
where row_no = 1;
OUTPUT:
2016-03-01 112 81314 Denise
2016-03-02 59 39091 Ruby
2016-03-03 51 18105 Roy
2016-03-04 49 533 Patrick
2016-03-05 49 7891 Stephanie
2016-03-06 49 84307 Evelyn
2016-03-07 35 80682 Deborah
2016-03-08 35 10985 Timothy
2016-03-09 35 31221 Susan
2016-03-10 35 43192 Bobby
2016-03-11 35 3178 Melissa
2016-03-12 35 54967 Kenneth
2016-03-13 35 30061 Julia
2016-03-14 35 32353 Rose
2016-03-15 35 27789 Helen
still wrong
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 cons_submission as ( select h.hacker_id,h.name,s.submission_date, dense_rank() over (partition by submission_date,h.hacker_id order by submission_date asc) as cons_sub from hacker h inner join submissions s on h.hacker_id = s.hacker_id),
hacker_XV as ( select hacker_id,name from cons_submission group by hacker_id,name having max(cons_sub) = 15),
hackers_fltrd as ( select cs.hacker_id,cs.name,cs.submission_date, count(*) as submitted_count from cons_submission cs inner join hacker_XV hxv on cs.hacker_id = hxv.hacker_id group by hacker_id,name,submission_date ),
cte as ( select hacker_id,name,submission_date,submitted_count, row_number() over (partition by submission_date order by submitted_count desc, hacker_id asc ) as row_no from hackers_fltrd )
select submission_date,submitted_count,hacker_id,name from cte where row_no = 1;
OUTPUT: 2016-03-01 112 81314 Denise 2016-03-02 59 39091 Ruby 2016-03-03 51 18105 Roy 2016-03-04 49 533 Patrick 2016-03-05 49 7891 Stephanie 2016-03-06 49 84307 Evelyn 2016-03-07 35 80682 Deborah 2016-03-08 35 10985 Timothy 2016-03-09 35 31221 Susan 2016-03-10 35 43192 Bobby 2016-03-11 35 3178 Melissa 2016-03-12 35 54967 Kenneth 2016-03-13 35 30061 Julia 2016-03-14 35 32353 Rose 2016-03-15 35 27789 Helen still wrong