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
| Column | Type |
|---|---|
| airline_code | char |
| airline_name | varchar |
Primary key: airline_code.
One row per carrier.
Table: Flight
| Column | Type |
|---|---|
| flight_id | int |
| airline_code | char |
| flight_date | date |
| status | enum(operated, cancelled) |
| arrival_delay_min | int |
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_code | airline_name |
|---|---|
| 6E | IndiGo |
| AI | Air India |
| SG | SpiceJet |
| QP | Akasa Air |
Flight
| flight_id | airline_code | flight_date | status | arrival_delay_min |
|---|---|---|---|---|
| 1 | 6E | 2024-08-01 | operated | -4 |
| 2 | 6E | 2024-08-01 | operated | 15 |
| 3 | 6E | 2024-08-02 | operated | 16 |
| 4 | AI | 2024-08-01 | operated | 42 |
| 5 | AI | 2024-08-02 | cancelled | NULL |
| 6 | AI | 2024-08-02 | operated | 3 |
| 7 | SG | 2024-08-03 | cancelled | NULL |
| 8 | 6E | 2024-08-03 | operated | 0 |
Output
| airline_name | operated | cancelled | on_time_pct |
|---|---|---|---|
| IndiGo | 4 | 0 | 75 |
| Air India | 2 | 1 | 50 |
| SpiceJet | 0 | 1 | NULL |
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_nameAnother 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 →