Flight Delay Impact
ASKED IN INTERVIEWExpedia
Problem Statement
Expedia wants to measure the "Customer Frustration Index." A critical threshold for frustration is a delay exceeding 4 hours.
Write an SQL query to find the total number of unique users affected by such delays and the average delay they experienced.
Rules:
- A delay is calculated as (actual_departure - scheduled_departure).
- Only include flights where the delay is >= 4 hours.
- If a flight was cancelled, it does not count as a "delay" for this specific metric.
- Output columns: affected_user_count, average_delay_hours.
- Round average_delay_hours to 2 decimal places.
- Results must be returned in descending order of affected_user_count.
Table Schema:
- Flights: flight_id, scheduled_departure (TIMESTAMP), actual_departure (TIMESTAMP), flight_number.
- Bookings: booking_id, flight_id, user_id, seat_class.
Tests your understanding of
Joins, DateTime Arithmetic, Aggregation and Filtering
Input Tables
Flights
| flight_id(INTEGER) | scheduled_departure(TIMESTAMP) | actual_departure(TIMESTAMP) |
|---|---|---|
| 1 | 2026-02-13 10:00:00 | 2026-02-13 15:00:00 |
| 2 | 2026-02-13 11:00:00 | 2026-02-13 12:00:00 |
| 3 | 2026-02-13 14:00:00 | 2026-02-13 19:30:00 |
| 4 | 2026-02-13 20:00:00 | 2026-02-14 01:00:00 |
| 5 | 2026-02-13 08:00:00 | 2026-02-13 08:15:00 |
Bookings
| flight_id(INTEGER) | user_id(INTEGER) |
|---|---|
| 1 | 501 |
| 1 | 502 |
| 3 | 503 |
| 3 | 504 |
| 4 | 505 |
| 4 | 501 |
| 2 | 506 |
Expected Output
| affected_user_count(INTEGER) | average_delay_hours(DECIMAL) |
|---|---|
| 5 | 5.17 |
Tags
MediumASKED IN INTERVIEWJoinsDateTime ArithmeticAggregationFiltering
15-20 min
51%