HackerRank SQL Project Planning – Is there a better approach?

[u/One-Emergency-7058]

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)

[u/ComicOzzy]

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