Median Departure Delay per Airport — SQL Problem
Average delays are skewed by a few disasters, so the operations dashboard reports the median departure delay at each origin airport: the middle value of its…
- Difficulty: Hard
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #544
Problem statement
Average delays are skewed by a few disasters, so the operations dashboard reports the median departure delay at each origin airport: the middle value of its delays in sorted order, or the average of the two middle values when the count is even.
Return origin, departures (the number of departures from it) and median_delay, rounded to 1 decimal, for every airport with at least one departure. Return the rows in any order.
Tables
Table: Departure
| Column | Type |
|---|---|
| departure_id | int |
| flight_no | varchar |
| origin | char |
| delay_min | int |
Primary key: departure_id.
One row per departure; delay_min is minutes late (negative when it left early) and is never NULL.
Examples
Example 1
Departure
| departure_id | flight_no | origin | delay_min |
|---|---|---|---|
| 1 | 6E 512 | BLR | 5 |
| 2 | AI 503 | BLR | 120 |
| 3 | UK 811 | BLR | 0 |
| 4 | 6E 6031 | DEL | 15 |
| 5 | SG 8169 | DEL | -3 |
| 6 | AI 865 | DEL | 15 |
| 7 | QP 1301 | DEL | 48 |
| 8 | 6E 371 | GOI | 22 |
Output
| origin | departures | median_delay |
|---|---|---|
| BLR | 3 | 5 |
| DEL | 4 | 15 |
| GOI | 1 | 22 |
How to solve Median Departure Delay per Airport
SQL has no portable MEDIAN, so it is computed from positions. Number each airport's departures by delay with ROW_NUMBER() OVER (PARTITION BY origin ORDER BY delay_min, departure_id) and attach the group size with COUNT(*) OVER (PARTITION BY origin). The median sits at positions FLOOR((n+1)/2) and CEIL((n+1)/2): for n = 3 both are 2, for n = 4 they are 2 and 3. Keeping those one or two rows and averaging their delays gives the median in both cases.
The tie-breaker on departure_id only makes the numbering unique; equal delays are interchangeable, so the median value does not depend on it. MAX(cnt) (every row carries the same count) reports the number of departures, and the result is rounded to one decimal — a median of whole minutes is either whole or ends in .5.
The alternative replaces the window functions with correlated counts: a row's position is the number of rows of its airport that sort before or equal to it, and 2 * rn BETWEEN n AND n + 2 is another way to say "one of the middle positions". That is quadratic per airport; the window version is one sort.
Reference solution (MySQL)
WITH ordered AS (
SELECT origin, delay_min,
ROW_NUMBER() OVER (PARTITION BY origin ORDER BY delay_min, departure_id) AS rn,
COUNT(*) OVER (PARTITION BY origin) AS cnt
FROM Departure
)
SELECT origin, MAX(cnt) AS departures, ROUND(AVG(delay_min), 1) AS median_delay
FROM ordered
WHERE rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2))
GROUP BY originAnother way
WITH pos AS (
SELECT d.origin, d.delay_min,
(SELECT COUNT(*) FROM Departure e WHERE e.origin = d.origin
AND (e.delay_min < d.delay_min OR (e.delay_min = d.delay_min AND e.departure_id <= d.departure_id))) AS rn,
(SELECT COUNT(*) FROM Departure e WHERE e.origin = d.origin) AS cnt
FROM Departure d
)
SELECT origin, MIN(cnt) AS departures, ROUND(SUM(delay_min) / COUNT(*), 1) AS median_delay
FROM pos
WHERE 2 * rn BETWEEN cnt AND cnt + 2
GROUP BY origin← Repeat-Guest Retention Within 90 Days by Cohort · Nightly Rooms Sold Calendar for the Holiday Season →