S06 · interview simulation
SQL gym: rolling and same-weekday features with window functions
People, data and outputs in this scenario are simulated. Each stage is recorded when you commit; the reveal comes after.
- 5stages
- 30 minabout
- 0stages committed
The brief
Simulated interviewer: "Using the airline sample database, produce one row per flight and departure date with
passengers, the average passengers of the same flight over the previous 7 calendar days (excluding the current
day), and passengers on the same weekday one week earlier. These will be forecasting features."
Tables (synthetic): flight_day (clean, one row per flight_no x date, 2022-2024, every flight daily) and
flight_day_raw (messy copy for 2022-2023 with duplicate rows and date gaps, including a 9-day IST-TZX gap from
2023-03-04 to 2023-03-12). Several routes have more than one flight (IST-ESB: ZX101, ZX102, ZX103). This is a
structured task: you choose and justify, no code is executed.
Stage 1 of 5
Work on the clean flight_day table first. Candidate expressions for the 7-day trailing average:
- (a)
AVG(passengers) OVER (PARTITION BY flight_no ORDER BY date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) - (b)
AVG(passengers) OVER (PARTITION BY route ORDER BY date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) - (c)
AVG(passengers) OVER (PARTITION BY flight_no ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) - (d)
AVG(passengers) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING)
Which expression is correct on flight_day?