Merge Overlapping Time Periods | SQL Joins Interview Questions

[@florincopaci6821]

My approach:
with flag as(
select startdate, enddate,
case when lag(enddate,1) over (order by startdate) >= startdate then 0 else 1 end as flag
from SequenceData
) , grp as (
select startdate, enddate, sum(flag) over ( order by startdate) as grp
from flag
)
select min(startdate) as startdate, max(enddate) as enddate
from grp
group by grp;
Hope it helps.

[@SMM-j7j]

Nice video bro. Good concept.

[@datasculptor2895]

Thanks a ton

[@HARSHRAJ-gp6ve]

with cte as(
SELECT SequenceData.*,LEAD(StartDate)OVER() as d1 FROM SequenceData
),cte1 as(
select StartDate,EndDate FROM cte where d1>EndDate or d1 is null
),cte2 as(
select cte1.*,LAG(EndDate)OVER() as date2 FROM cte1
),cte3 as(
select StartDate,EndDate,COALESCE(ADDDATE(date2,1),(select MIN(startDate) FROM SequenceData)) as date3 FROM
cte2
)
select date3,EndDate FROM cte3;
+---------------------+---------------------+
| date3 | EndDate |
+---------------------+---------------------+
| 2024-01-01 00:00:00 | 2024-01-09 00:00:00 |
| 2024-01-10 00:00:00 | 2024-01-11 00:00:00 |
| 2024-01-12 00:00:00 | 2024-01-20 00:00:00 |
+---------------------+---------------------+