S02 · code review simulation

Review a "busiest flight per route" query that looks right

People, data and outputs in this scenario are simulated. Each stage is recorded when you commit; the reveal comes after.

4stages
35 minabout
0stages committed

The brief

A BI developer (simulated) asks for a review of the query behind a dashboard tile that shows, for each route, the busiest flight of 2024 and its context. The tables are the course's synthetic flight_day (one row per flight and date); a route can have up to three flights a day. The developer's note: "Output looks right for IST-CDG, shipping tomorrow."

WITH ranked AS (
  SELECT route, date, flight_no, load_factor, passengers, avg_fare,
         ROW_NUMBER() OVER (PARTITION BY route ORDER BY load_factor DESC)        AS rn,
         SUM(passengers) OVER (PARTITION BY route ORDER BY date)                 AS running_pax,
         LAST_VALUE(avg_fare) OVER (PARTITION BY route ORDER BY date)            AS latest_fare
  FROM flight_day
  WHERE date >= '2024-01-01'
)
SELECT route, date AS busiest_date, flight_no, load_factor,
       running_pax, latest_fare
FROM ranked
WHERE rn = 1;

Tile captions (from the dashboard spec): "Busiest flight of 2024", "Passengers carried on the route up to that flight", "Latest fare on the route".

Stage 1 of 4

Review the SQL above against the three captions. Dates are ISO text (YYYY-MM-DD) in SQLite.

Which findings are correct? Select all that apply.

`ROW_NUMBER()` with `ORDER BY load_factor DESC` has no tie-break, so which tied flight appears depends on how the engine reads the rows.`LAST_VALUE(avg_fare)` with only an ORDER BY uses the default frame, which ends at the current row's peers, so it returns the busiest date's fare.`SUM(...) OVER (... ORDER BY date)` counts every flight of that date, because equal sort values share a frame: 'through the end of the date', not 'up to that flight'.`WHERE date >= '2024-01-01'` is wrong because dates stored as text cannot be compared with >=, so the filter silently returns the wrong rows.A window result cannot be filtered with `WHERE rn = 1`, so the outer query needs a HAVING clause or the filter must move into the same SELECT.