https://www.youtube.com/watch?v=Vle2Udfdg1g

-- Create the service_log table
CREATE TABLE service_log (
    service_name NVARCHAR(50),
    updated_time DATETIME,
    status NVARCHAR(10)
);

-- Insert sample data into the service_log table
INSERT INTO service_log (service_name, updated_time, status)
VALUES
    ('hdfs', '2024-03-06 10:00:00', 'up'),
    ('hdfs', '2024-03-06 10:01:00', 'up'),
    ('hdfs', '2024-03-06 10:02:00', 'down'),
    ('hdfs', '2024-03-06 10:03:00', 'down'),
    ('hdfs', '2024-03-06 10:04:00', 'down'),
    ('hdfs', '2024-03-06 10:05:00', 'down'),
    ('hdfs', '2024-03-06 10:06:00', 'down'),
    ('hdfs', '2024-03-06 10:07:00', 'up'),
    ('hdfs', '2024-03-06 10:08:00', 'up'),
    ('hdfs', '2024-03-06 10:09:00', 'down'),
    ('hdfs', '2024-03-06 10:10:00', 'down');

-- Select data to confirm the insertion
SELECT * FROM service_log;

WITH
ROW_NUMBER()

with cte1 as(
select *
	,row_number() OVER (order by updated_time) as rn
from service_log
)
,cte2 as (
select *
	,row_number() OVER (order by updated_time) as rn1
from service_log
where status = 'down')
,cte3 as (
select c1.service_name,c1.updated_time,c1.status
	,c1.rn-c2.rn1 as row_diff
from cte1 c1 left join cte2 c2 ON c1.updated_time = c2.updated_time
)

select 
	service_name
    ,status
	,MIN(updated_time) as Start_time
    ,MAX(updated_time) as End_time
from cte3
group by row_diff
HAVING COUNT(*) >=5;