Interview Question Bank · Gaps and islands · two states · ROW_NUMBER difference
11. Success and failure periods
HardGeneralQuestion #203
Concept: Gaps and islands · two states · ROW_NUMBER differenceshow
To collapse a daily log into periods of the same state, number the rows twice: once overall and once within each state. Their difference stays constant along an unbroken run of one state and changes when the run is interrupted. Group by the state and that difference (the difference alone can repeat across states) and report MIN and MAX date. Filter to the reporting window before numbering, so periods are cut at its edges.
Example (different data):
SELECT day, mood, ROW_NUMBER() OVER (ORDER BY day) - ROW_NUMBER() OVER (PARTITION BY mood ORDER BY day) AS run_id FROM mood_log;
Your task
Kestrel Data's nightly backup job runs once a day and either succeeds or fails. The reliability report for the first 20 days of January 2025 lists each unbroken stretch of the same outcome.
Using only runs from 2025-01-01 to 2025-01-20, return outcome, start_date, end_date and days (the number of runs in the period) for every period of consecutive runs with the same outcome. Sort by start_date.
Starting Postgres in your browser…
Checking your access…