S06 · interview simulation
SQL gym: booking pace against the same weekday last year
People, data and outputs in this scenario are simulated. Each stage is recorded when you commit; the reveal comes after.
- 5stages
- 40 minabout
- 0stages committed
The brief
A revenue manager asks for one number per departure week: "Are we ahead or behind last year at 21 days out?" You use the
synthetic airline database (SQLite): booking_curve(date, flight_no, days_to_departure, bookings, cumulative_bookings)
has one row per flight, departure date and snapshot, with snapshots at 90, 60, 45, 30, 21, 14, 10, 7, 5, 3, 2, 1 and 0
days out; calendar(date, weekday, month, is_holiday, holiday_name, event).
The report week is departures from 2024-06-13 to 2024-06-19 (7 days, 19 flights, so 133 flight-days). The comparison for a departure is the same flight on the same weekday last year. All results below were recomputed on the sample database.
Stage 1 of 5
2024-06-13 is a Thursday. You need the comparison date for each departure so that the weekday matches.
Which join condition gives the same flight on the same weekday last year?