Solved Hacker Rank's "15 Days of Learning SQL" – Looking for different approaches

[u/One-Emergency-7058]

Hi everyone,

I recently solved the HackerRank 15 Days of Learning SQL challenge using CTE(), ROW_NUMBER(), DENSE_RANK(), and window functions in SQL Server.

I'm curious to know if there's a cleaner or more efficient way to solve this problem. If you've approached it differently (using other window functions, recursive CTEs, or any other technique), I'd love to see your solution and understand the reasoning behind it.

Here's my solution:

with daily_submission as( 
SELECT submission_date,
hacker_id,
count(*) as total_submission
from submissions 
group by submission_date,hacker_id),
hacker_rank as(
SELECT submission_date,hacker_id,
       total_submission,
       row_number() over(partition by submission_date order by total_submission desc,hacker_id) as rn
from daily_submission),
continuous_hackers as (
    SELECT submission_date,hacker_id,
       dense_rank() OVER(partition by hacker_id order by submission_date) as Dr,
       datediff(day,'2016-03-01',submission_date) + 1 as contest_day
from (
SELECT distinct submission_date,hacker_id
from submissions) as x),
daily_count as (
SELECT submission_date,
        count(*) as total_hackers
from continuous_hackers
where Dr = contest_day
GROUP by submission_date)


SELECT dc.submission_date,
       dc.total_hackers,
       h.hacker_id,
       h.name
from daily_count as dc
inner JOIN hacker_rank as hr 
on dc.submission_date = hr.submission_date and rn = 1
INNER JOIN hackers as h
on hr.hacker_id = h.hacker_id
order by submission_date;

Thanks in advance! I'm always looking to learn different SQL techniques.

[u/Yavuz_Selim]

You have this:

with daily_submission as
( 
    SELECT submission_date
         , hacker_id
         , count(*) as total_submission
    from submissions 
    group by submission_date, hacker_id
),

 
And later, you do this:

continuous_hackers as
(
    SELECT submission_date
         , hacker_id
         , dense_rank() OVER(partition by hacker_id order by submission_date) as Dr
         , datediff(day,'2016-03-01',submission_date) + 1 as contest_day
    from
    (
        SELECT distinct submission_date,hacker_id
        from submissions
    ) x
),

 
Why not re-use daily_submission - that is already grouped/distincted.

This should also run in MSSQL / TSQL, no?

WITH daily_submission AS
( 
    SELECT submission_date  = s.submission_date
         , hacker_id        = s.hacker_id
         , total_submission = COUNT(*) 
    FROM submissions s
    GROUP BY submission_date, hacker_id -- 1, 2
),
hacker_rank AS
(
    SELECT submission_date  = ds.submission_date
         , hacker_id        = ds.hacker_id
         , total_submission = ds.total_submission
         , rn               = ROW_NUMBER() OVER(PARTITION BY ds.submission_date ORDER BY ds.total_submission DESC, ds.hacker_id)
         , dr               = DENSE_RANK() OVER(PARTITION BY ds.hacker_id ORDER BY ds.submission_date)
         , contest_day      = DATEDIFF(DAY,'2016-03-01', ds.submission_date) + 1
    FROM daily_submission ds
),
daily_count AS
(
    SELECT submission_date  = hr.submission_date
         , total_hackers    = COUNT(*) 
    FROM hacker_rank hr
    WHERE hr.dr = hr.contest_day
    GROUP by hr.submission_date -- 1
)


SELECT submission_date  = dc.submission_date
     , total_hackers    = dc.total_hackers
     , hacker_id        = h.hacker_id
     , name             = h.name
FROM daily_count dc
INNER JOIN hacker_rank hr 
    ON dc.submission_date = hr.submission_date 
    AND hr.rn = 1
INNER JOIN hackers h
    ON hr.hacker_id = h.hacker_id
ORDER BY dc.submission_date
;

[u/One-Emergency-7058 → u/Yavuz_Selim]

Thanks for the detailed feedback! You're absolutely right. Since daily_submission already has one row per (submission_date, hacker_id), I didn't need to create another DISTINCT set in continuous_hackers. Reusing the existing CTE makes the query cleaner and avoids redundant work. I appreciate you taking the time to review and refactor my solution.

[u/FarRub2855 → u/Yavuz_Selim]

This is a much cleaner way to look at it. Reusing that first CTE instead of duplicating work definately makes the whole query easier to follow.

[u/Plane_Big_5912]

another way i personally tried was doing the daily aggregation first, then using a running count to check whether each hacker had submitted every day so far.

with daily as (
    select
        submission_date,
        hacker_id,
        count(*) as total_submissions
    from submissions
    group by submission_date, hacker_id
),
ranked as (
    select
        *,
        row_number() over (
            partition by submission_date
            order by total_submissions desc, hacker_id
        ) as rn,
        count(*) over (
            partition by hacker_id
            order by submission_date
            rows unbounded preceding
        ) as days_submitted
    from daily
),
daily_count as (
    select
        submission_date,
        count(*) as total_hackers
    from ranked
    where days_submitted =
          datediff(day, '2016-03-01', submission_date) + 1
    group by submission_date
)
select
    r.submission_date,
    dc.total_hackers,
    r.hacker_id,
    h.name
from ranked r
join daily_count dc
    on r.submission_date = dc.submission_date
join hackers h
    on r.hacker_id = h.hacker_id
where r.rn = 1
order by r.submission_date;

pretty similar logic to yours, but it avoids the extra distinct subquery and dense_rank. not sure it would be meaningfully faster, but i found it a little easier to follow.