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

[@ishamajumdar5580]

WITH cte AS ( SELECT *, MINUTE(updated_time) - ROW_NUMBER() OVER(PARTITION BY status ORDER BY MINUTE(updated_time)) AS diff FROM service_log WHERE status = 'down' ) SELECT service_name, status, MIN(updated_time) AS start_time, MAX(updated_time) AS end_time FROM cte GROUP BY service_name, status, diff HAVING COUNT(*) >= 5;

[@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;