Leetcode Hard 1225: Find Continuous Dates META Advanced SQL Data Science Interview Question Solved!

[@anuragshrivastava7855]

bdiya bhai

[@roymou5856]

awesome, thanks. i was thinking we can filter out unwanted dates at the very beginning itself:
with cte as
(
select * from
(
select
fail_date as dateState,
'failed' as state
from Failed
union
select
success_date as dateState,
'succeeded' as state
from Succeeded
) t
where dateState between '2019-01-01' and '2019-12-31'
order by dateState
),
rnkDate as
(
select
dateState,
state,
rank() over (partition by state order by dateState) as rnkDate
from cte
),
grpMarker as
(
select
*,
date_sub(dateState,interval rnkDate DAY) as grpMarker
from rnkDate
)
select
state as period_state ,
min(dateState) as start_date ,
max(dateState) as end_date
from grpMarker
group by grpMarker, state

[@EverydayDataScience]

Yes, filtering early in real-world problems especially when dealing with millions of rows is usually a better option. Since I was trying to demonstrate the concept, I filtered it at the very end.

[@roymou5856]

@@EverydayDataScience thank you making all these videos. it helped me a lot gaining my confidence to attempt writing medium and hard SQL. Great job. Please share more videoa and help us learning the key concepts.

[@xiamojq621]

thank bro

[@EverydayDataScience]

Glad that you found the video useful 😊