Amazon SQL Interview Question: Solving Common SQL Challenges with Real-World Examples

[@HARSHRAJ-gp6ve]

with cte as(
select service_log.*,ROW_NUMBER()OVER()as r1 FROM service_log
),cte1 as(
select cte.*,ROW_NUMBER()OVER(PARTITION BY status ORDER BY updated_time) as r2 FROM cte
),cte2 as(
select service_name,updated_time,status,(r1-r2) as r3 FROM cte1
),cte3 as(
select r3,MIN(updated_time) as start_time,MAX(updated_time) as end_time FROM cte2 group by r3
having count(*)>=5
)
select service_name,status,start_time,end_time FROM cte3 JOIN cte2 where cte3.r3=cte2.r3 and
cte3.start_time=cte2.updated_time or cte3.end_time=cte2.updated_time LIMIT 1;