What is the issue with my code? HackerRank SQL – 15 Days of Learning SQL

December 13, 2021 Traci

Question :- Julia conducted a 15 days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016. (https://www.hackerrank.com/challenges/15-days-of-learning-sql/problem?isFullScreen=true)

Write a query to print total number of unique hackers who made at least 1 submission each day (starting on the first day of the contest), and find the hacker_id and name of the hacker who made maximum number of submissions each day. If more than one such hacker has a maximum number of submissions, print the lowest hacker_id. The query should print this information for each day of the contest, sorted by the date.

I am not getting the correct output for 2016-03-01 to 2016-03-06.

;with cte as (
select hacker_id, count(distinct submission_date) as SubCnt 
from Submissions group by hacker_id
), dateMaxcte as 
(
select tmp.submission_date, tmp.max_hacker_id, h.name from 
    (select submission_date, hacker_id as max_hacker_id, row_number()           over(partition by submission_date order by count(1) desc,hacker_id)         Rn from Submissions group by submission_date, hacker_id
    ) tmp inner join Hackers h 
            on h.hacker_id = tmp.max_hacker_id    
            where tmp.Rn=1
), joinMain_cte as
(
    select s.submission_date , s.hacker_id, cte.SubCnt
    from Submissions s inner join cte
    on s.hacker_id = cte.hacker_id
),  joinMain_cte2 as 
(
select submission_date, count(distinct hacker_id) as hacker_idCnt
from joinMain_cte where SubCnt=15 
group by submission_date 
) select s.submission_date, s.hacker_idCnt, d.max_hacker_id, d.name
    from joinMain_cte2 s
    inner join dateMaxcte d on s.submission_date = d.submission_date
    order by submission_date;

Desired 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

My Output :-

2016-03-01 35 81314 Denise
2016-03-02 35 39091 Ruby
2016-03-03 35 18105 Roy
2016-03-04 35 533 Patrick
2016-03-05 35 7891 Stephanie
2016-03-06 35 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

Category: Finance Law Tags: sql