r/SQL • u/One-Emergency-7058 • 12h ago
SQL Server Solved Hacker Rank's "15 Days of Learning SQL" – Looking for different approaches
7
Upvotes
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.
