On-Time Arrival Performance by Airline

Medium
533.

On-Time Arrival Performance by Airline

The aviation regulator counts an operated flight as on time when it arrives no more than 15 minutes after schedule (early arrivals have a negative delay and are on time). Cancelled flights have arrival_delay_min NULL and are counted separately.

For every airline with at least one flight in Flight, return airline_name, operated (operated flights), cancelled (cancelled flights) and on_time_pct = 100 × on-time flights / operated flights, rounded to 2 decimals — NULL when the airline operated no flight. Return the rows in any order.

Tables

Table: Airline

ColumnType
airline_codechar
airline_namevarchar

Primary key: airline_code.

One row per carrier.

Table: Flight

ColumnTypeValues
flight_idint
airline_codechar
flight_datedate
statusenumoperated, cancelled
arrival_delay_minint

Primary key: flight_id.

arrival_delay_min is minutes late on arrival (negative when early), and NULL exactly when the flight was cancelled.

Example

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

Airline

airline_codeairline_name
6EIndiGo
AIAir India
SGSpiceJet
QPAkasa Air

Flight

flight_idairline_codeflight_datestatusarrival_delay_min
16E2024-08-01operated-4
26E2024-08-01operated15
36E2024-08-02operated16
4AI2024-08-01operated42
5AI2024-08-02cancelledNULL
6AI2024-08-02operated3
7SG2024-08-03cancelledNULL
86E2024-08-03operated0

Output

airline_nameoperatedcancelledon_time_pct
IndiGo4075
Air India2150
SpiceJet01NULL
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.

On-Time Arrival Performance by Airline — SQL Problem

The aviation regulator counts an operated flight as on time when it arrives no more than 15 minutes after schedule (early arrivals have a negative delay and…

  • Difficulty: Medium
  • Topics: Conditional Logic, Aggregation, Joins
  • Dialect: MySQL
  • Problem: #533

Problem statement

The aviation regulator counts an operated flight as on time when it arrives no more than 15 minutes after schedule (early arrivals have a negative delay and are on time). Cancelled flights have arrival_delay_min NULL and are counted separately.

For every airline with at least one flight in Flight, return airline_name, operated (operated flights), cancelled (cancelled flights) and on_time_pct = 100 × on-time flights / operated flights, rounded to 2 decimals — NULL when the airline operated no flight. Return the rows in any order.

Tables

Table: Airline

ColumnType
airline_codechar
airline_namevarchar

Primary key: airline_code.

One row per carrier.

Table: Flight

ColumnType
flight_idint
airline_codechar
flight_datedate
statusenum(operated, cancelled)
arrival_delay_minint

Primary key: flight_id.

arrival_delay_min is minutes late on arrival (negative when early), and NULL exactly when the flight was cancelled.

Examples

Example 1

Airline

airline_codeairline_name
6EIndiGo
AIAir India
SGSpiceJet
QPAkasa Air

Flight

flight_idairline_codeflight_datestatusarrival_delay_min
16E2024-08-01operated-4
26E2024-08-01operated15
36E2024-08-02operated16
4AI2024-08-01operated42
5AI2024-08-02cancelledNULL
6AI2024-08-02operated3
7SG2024-08-03cancelledNULL
86E2024-08-03operated0

Output

airline_nameoperatedcancelledon_time_pct
IndiGo4075
Air India2150
SpiceJet01NULL

How to solve On-Time Arrival Performance by Airline

Three numbers per airline, each counting a different subset of the same rows, is the classic case for conditional aggregation: group once by airline and sum a CASE that is 1 for the rows of interest and 0 otherwise. Operated and cancelled come straight from the status; on-time adds the condition arrival_delay_min <= 15, which is inclusive and also accepts early (negative) arrivals.

The percentage divides on-time flights by operated flights — not by all flights, since a cancelled flight is neither on time nor late. An airline whose only flights were cancelled has an operated count of 0, and dividing by zero is an error in some engines and NULL in others; NULLIF(…, 0) turns the denominator into NULL, so the percentage is NULL everywhere, as asked. Then round to two decimals.

The alternative leans on NULL semantics: COUNT(arrival_delay_min) counts only operated flights, and AVG of a 1/0 flag that is NULL for cancellations is exactly the on-time share — it ignores NULLs and returns NULL for an empty set. One grouped pass either way.

Reference solution (MySQL)

SELECT a.airline_name,
       SUM(CASE WHEN f.status = 'operated' THEN 1 ELSE 0 END) AS operated,
       SUM(CASE WHEN f.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
       ROUND(100 * SUM(CASE WHEN f.status = 'operated' AND f.arrival_delay_min <= 15 THEN 1 ELSE 0 END)
             / NULLIF(SUM(CASE WHEN f.status = 'operated' THEN 1 ELSE 0 END), 0), 2) AS on_time_pct
FROM Airline a
JOIN Flight f ON f.airline_code = a.airline_code
GROUP BY a.airline_code, a.airline_name

Another way

SELECT a.airline_name, COUNT(f.arrival_delay_min) AS operated, COUNT(*) - COUNT(f.arrival_delay_min) AS cancelled,
       ROUND(100 * AVG(IF(f.arrival_delay_min IS NULL, NULL, IF(f.arrival_delay_min <= 15, 1, 0))), 2) AS on_time_pct
FROM Flight f JOIN Airline a ON a.airline_code = f.airline_code
GROUP BY a.airline_code, a.airline_name

← Average Daily Rate by Hotel and Month · Waitlist Positions on a Train per Class →