Flights Delayed Three or More Days in a Row

Hard
541.

Flights Delayed Three or More Days in a Row

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

ColumnType
flight_novarchar
flight_datedate
departure_delay_minint

Primary key (together): 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).

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

DailyFlight

flight_noflight_datedeparture_delay_min
6E 21342024-12-0145
6E 21342024-12-0231
6E 21342024-12-0380
6E 21342024-12-0430
6E 21342024-12-0555
6E 21342024-12-0662
6E 21342024-12-0744
AI 5052024-12-0190
AI 5052024-12-02120
AI 5052024-12-0475
1 more row

Output

flight_nostreak_startstreak_enddelayed_days
6E 21342024-12-012024-12-033
6E 21342024-12-052024-12-073
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
flight_novarchar
flight_datedate
departure_delay_minint

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_noflight_datedeparture_delay_min
6E 21342024-12-0145
6E 21342024-12-0231
6E 21342024-12-0380
6E 21342024-12-0430
6E 21342024-12-0555
6E 21342024-12-0662
6E 21342024-12-0744
AI 5052024-12-0190
AI 5052024-12-02120
AI 5052024-12-0475
AI 5052024-12-0540

Output

flight_nostreak_startstreak_enddelayed_days
6E 21342024-12-012024-12-033
6E 21342024-12-052024-12-073

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(*) >= 3

Another 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 →