Flights Departing More Than an Hour Late — SQL Problem
The airline's ops desk files a delay report for every departure that left more than 60 minutes after its scheduled time.
- Difficulty: Easy
- Topics: Basics, Dates
- Dialect: MySQL
- Problem: #525
Problem statement
The airline's ops desk files a delay report for every departure that left more than 60 minutes after its scheduled time. A cancelled flight has actual_departure NULL and is not a delay.
Return flight_no, scheduled_departure and delay_minutes (whole minutes between the scheduled and actual departure) for every such flight. A flight exactly 60 minutes late is not reported. Return the rows in any order.
Tables
Table: FlightDeparture
| Column | Type |
|---|---|
| departure_id | int |
| flight_no | varchar |
| origin | char |
| destination | char |
| scheduled_departure | datetime |
| actual_departure | datetime |
Primary key: departure_id.
One row per operated (or cancelled) flight; times are local and on the minute. actual_departure is NULL for a cancelled flight.
Examples
Example 1
FlightDeparture
| departure_id | flight_no | origin | destination | scheduled_departure | actual_departure |
|---|---|---|---|---|---|
| 1 | 6E 512 | BLR | DEL | 2024-07-01 06:10:00 | 2024-07-01 06:25:00 |
| 2 | AI 803 | DEL | BOM | 2024-07-01 09:00:00 | 2024-07-01 10:45:00 |
| 3 | UK 829 | BOM | MAA | 2024-07-01 21:30:00 | 2024-07-01 22:30:00 |
| 4 | SG 116 | GOI | BLR | 2024-07-01 23:20:00 | 2024-07-02 01:05:00 |
| 5 | 6E 219 | HYD | CCU | 2024-07-02 07:40:00 | NULL |
| 6 | QP 1401 | BOM | BLR | 2024-07-02 13:15:00 | 2024-07-02 14:16:00 |
Output
| flight_no | scheduled_departure | delay_minutes |
|---|---|---|
| AI 803 | 2024-07-01 09:00:00 | 105 |
| SG 116 | 2024-07-01 23:20:00 | 105 |
| QP 1401 | 2024-07-02 13:15:00 | 61 |
How to solve Flights Departing More Than an Hour Late
The delay of a flight is the gap between two datetimes, TIMESTAMPDIFF(MINUTE, scheduled_departure, actual_departure). Comparing full datetimes rather than just the clock times matters for a late-evening departure that leaves after midnight: 23:20 to 01:05 the next day is 105 minutes, while the clock times alone would look like a negative delay.
The report keeps flights where that difference is strictly greater than 60, so a flight exactly an hour late is left out and one at 61 minutes is in. The same expression is selected as delay_minutes.
Cancelled flights need no special case: with actual_departure NULL the difference is NULL, and NULL > 60 is not true, so the row is filtered out. The alternative compares the actual time with DATE_ADD(scheduled_departure, INTERVAL 60 MINUTE) instead, which can use an index on the departure time. Either way the query is one scan of the table.
Reference solution (MySQL)
SELECT flight_no, scheduled_departure,
TIMESTAMPDIFF(MINUTE, scheduled_departure, actual_departure) AS delay_minutes
FROM FlightDeparture
WHERE TIMESTAMPDIFF(MINUTE, scheduled_departure, actual_departure) > 60Another way
SELECT flight_no, scheduled_departure, TIMESTAMPDIFF(MINUTE, scheduled_departure, actual_departure) AS delay_minutes
FROM FlightDeparture
WHERE actual_departure IS NOT NULL AND actual_departure > DATE_ADD(scheduled_departure, INTERVAL 60 MINUTE)← Room Nights Sold per Hotel · Flight Bookings by Sales Channel →