Flights Delayed Three or More Days in a Row — SQL Problem
The airline's reliability team flags a flight number that is delayed by more than 30 minutes on at least 3 consecutive calendar days.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #541
Problem statement
The airline's reliability team flags a flight number that is delayed by more than 30 minutes on at least 3 consecutive calendar days. A day on which the flight did not operate (no row) breaks a run, and so does a day with a delay of 30 minutes or less.
Return one row per such run with flight_no, streak_start, streak_end and delayed_days (the length of the run). A flight can have several runs. Return the rows in any order.
Tables
Table: DailyFlight
| Column | Type |
|---|---|
| flight_no | varchar |
| flight_date | date |
| departure_delay_min | int |
Primary key: flight_no, flight_date.
One row per flight number per day it operated; departure_delay_min is minutes late (0 or negative when on time).
Examples
Example 1
DailyFlight
| flight_no | flight_date | departure_delay_min |
|---|---|---|
| 6E 2134 | 2024-12-01 | 45 |
| 6E 2134 | 2024-12-02 | 31 |
| 6E 2134 | 2024-12-03 | 80 |
| 6E 2134 | 2024-12-04 | 30 |
| 6E 2134 | 2024-12-05 | 55 |
| 6E 2134 | 2024-12-06 | 62 |
| 6E 2134 | 2024-12-07 | 44 |
| AI 505 | 2024-12-01 | 90 |
| AI 505 | 2024-12-02 | 120 |
| AI 505 | 2024-12-04 | 75 |
| AI 505 | 2024-12-05 | 40 |
Output
| flight_no | streak_start | streak_end | delayed_days |
|---|---|---|---|
| 6E 2134 | 2024-12-01 | 2024-12-03 | 3 |
| 6E 2134 | 2024-12-05 | 2024-12-07 | 3 |
How to solve Flights Delayed Three or More Days in a Row
This is a gaps-and-islands problem. After keeping only the days with a delay over 30 minutes, the runs are maximal sets of consecutive dates for one flight. The classic trick: number each flight's delayed days in date order with ROW_NUMBER(), and subtract that many days from the date. Inside a run both the date and the number go up by one per row, so flight_date - rn is constant; a missing day (no flight, or an on-time day removed by the filter) makes the date jump while the number does not, and the difference changes.
Grouping by (flight_no, grp) therefore gives one group per run; MIN and MAX of the date are its ends, COUNT(*) its length, and HAVING COUNT(*) >= 3 keeps the long ones. A delay of exactly 30 minutes is not "more than 30", so it is filtered out and splits a run like a missing day does.
The alternative builds the island id explicitly: LAG finds the previous delayed date, a day starts a new run unless that date was yesterday, and a running SUM of the start flags (with an explicit ROWS frame over a unique order) numbers the runs. Both need one sort per flight.
Reference solution (MySQL)
WITH late AS (
SELECT flight_no, flight_date,
DATE_SUB(flight_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY flight_no ORDER BY flight_date) DAY) AS grp
FROM DailyFlight
WHERE departure_delay_min > 30
)
SELECT flight_no, MIN(flight_date) AS streak_start, MAX(flight_date) AS streak_end, COUNT(*) AS delayed_days
FROM late
GROUP BY flight_no, grp
HAVING COUNT(*) >= 3Another way
WITH marked AS (
SELECT flight_no, flight_date,
CASE WHEN DATEDIFF(flight_date, LAG(flight_date) OVER (PARTITION BY flight_no ORDER BY flight_date)) = 1 THEN 0 ELSE 1 END AS starts_run
FROM DailyFlight WHERE departure_delay_min > 30
), runs AS (
SELECT flight_no, flight_date,
SUM(starts_run) OVER (PARTITION BY flight_no ORDER BY flight_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_id
FROM marked
)
SELECT flight_no, MIN(flight_date) AS streak_start, MAX(flight_date) AS streak_end, COUNT(*) AS delayed_days
FROM runs GROUP BY flight_no, run_id HAVING COUNT(*) >= 3← Connecting Itineraries per City Pair · Peak In-House Guests per Hotel →