Drill 1 of 3 in Travel questions
0

Interview Question Bank · COUNT FILTER · boundary · excluding cancellations

1. On-time performance by route

EasyTravelQuestion #206

Concept: COUNT FILTER · boundary · excluding cancellationsshow

On-time performance is the share of operated flights that arrived within a tolerance, so get three things right: which rows are in the denominator (flights that actually flew), which side of the tolerance the boundary falls on ("more than 15 minutes late" means exactly 15 is on time), and decimal division. COUNT(*) FILTER (WHERE ...) gives the on-time count in the same group as the total.

Example (different data):

SELECT line, COUNT(*) AS runs,
  COUNT(*) FILTER (WHERE minutes_late <= 5) AS punctual,
  ROUND(100.0 * COUNT(*) FILTER (WHERE minutes_late <= 5) / COUNT(*), 1) AS punctual_pct
FROM train_runs
WHERE NOT cancelled
GROUP BY line;

Your task

Ottergate Air publishes on-time figures for each route. A flight counts as late only when it arrives more than 15 minutes behind schedule.

For flights in tr_flights that operated (status 'landed'), return route (origin and destination joined with a hyphen, like 'PDX-DEN'), flights, on_time (arr_delay_min of 15 or less, early arrivals included) and on_time_pct (on_time as a % of flights, rounded to 1 decimal). Cancelled flights don't count at all. Sort by on_time_pct descending, then route.

Starting Postgres in your browser…

Checking your access…