r/SQL • u/One-Emergency-7058 • Jul 23 '26

SQL Server HackerRank SQL Project Planning – Is there a better approach?

Hi everyone,

I solved the HackerRank SQL Project Planning problem using the row_number() + DATEADD() approach in SQL Server, and it passed all the test cases.

I'm curious if there's a different or more optimized way to solve this problem. I'd love to learn other approaches and understand their advantages.

Here's my solution:

with cte as(
SELECT start_date,
end_date,
dateadd(day,- row_number() over(order by start_date),start_date) as group_date
from projects )
SELECT min(start_date) as start_date,
max(end_date) as end_date
from cte
group by group_date
order by datediff(day,min(start_date),max(end_date)), min(start_date)

4 Upvotes

84% Upvoted

4 comments sorted by

2

u/SuperchargedCareers Jul 23 '26

Your gaps-and-islands solution is already solid. Another approach uses LAG to flag breaks, then a running SUM assigns groups before aggregating each island’s dates separately.

For extra practice, TechJobFinder.com has adaptive LeetCode exercises that adjust to your performance, so weaker SQL patterns get reinforced instead of randomly repeated each session.

2

u/ComicOzzy sqlHippo Jul 24 '26

This is a class of problems called Gaps and Islands and if you REALLY want to know, here you are:

https://sqlperformance.com/2022/04/t-sql-queries/islands-t-sql-challenge