Flights Departing More Than an Hour Late

Easy
525.

Flights Departing More Than an Hour Late

EasyBasicsDates

MySQL

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

ColumnType
departure_idint
flight_novarchar
originchar
destinationchar
scheduled_departuredatetime
actual_departuredatetime

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.

Example

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

FlightDeparture

departure_idflight_noorigindestinationscheduled_departureactual_departure
16E 512BLRDEL2024-07-01 06:10:002024-07-01 06:25:00
2AI 803DELBOM2024-07-01 09:00:002024-07-01 10:45:00
3UK 829BOMMAA2024-07-01 21:30:002024-07-01 22:30:00
4SG 116GOIBLR2024-07-01 23:20:002024-07-02 01:05:00
56E 219HYDCCU2024-07-02 07:40:00NULL
6QP 1401BOMBLR2024-07-02 13:15:002024-07-02 14:16:00

Output

flight_noscheduled_departuredelay_minutes
AI 8032024-07-01 09:00:00105
SG 1162024-07-01 23:20:00105
QP 14012024-07-02 13:15:0061
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 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

ColumnType
departure_idint
flight_novarchar
originchar
destinationchar
scheduled_departuredatetime
actual_departuredatetime

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_idflight_noorigindestinationscheduled_departureactual_departure
16E 512BLRDEL2024-07-01 06:10:002024-07-01 06:25:00
2AI 803DELBOM2024-07-01 09:00:002024-07-01 10:45:00
3UK 829BOMMAA2024-07-01 21:30:002024-07-01 22:30:00
4SG 116GOIBLR2024-07-01 23:20:002024-07-02 01:05:00
56E 219HYDCCU2024-07-02 07:40:00NULL
6QP 1401BOMBLR2024-07-02 13:15:002024-07-02 14:16:00

Output

flight_noscheduled_departuredelay_minutes
AI 8032024-07-01 09:00:00105
SG 1162024-07-01 23:20:00105
QP 14012024-07-02 13:15:0061

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) > 60

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