Bonus studio 2: SQL cohort and window analytics on the airline sample database

A booking-pace cohort groups departures and asks what share of their final bookings had been sold at a given number of days out. Use departures in calendar year 2023.

1. Join flight_day to booking_curve on (date, flight_no) with no snapshot filter. Report the row count and SUM(bookings_final) against the same figures from flight_day alone. Explain the ratio in one sentence.
2. Build the matrix with one row per departure month and one column for each of D-60, D-30, D-14 and D-7. Each cell is the sum of cumulative_bookings at that snapshot divided by the sum at D-0, over all departures in the month. Write one query using conditional aggregation.
3. Check the D-0 sum of each month against SUM(bookings_final) for the same month in flight_day.
4. Cut the same share at D-30 by market for the whole year.
5. Write three sentences: how much do the month cohorts differ, how much do the market cohorts differ, and what that implies for using the month as a segmentation of booking pace.

Part A uses the complete table flight_day; part B uses the messy flight_day_raw.

1. For flight ZX118, for departures 2022-01-01 to 2023-12-31, compute passengers and a 7-day rolling mean (the current day and the 6 before it) with AVG(...) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).
2. Make the rolling mean NULL while the window holds fewer than 7 rows. Report how many rows that affects.
3. Add the value from the same weekday one year earlier with LAG(passengers, 364) and the percentage change. Report passengers, the rolling mean, the lagged value and the lag date for 2023-07-15 and 2023-12-31. Say why 364 and not 365.
4. For flight ZX109 in flight_day_raw, departures in 2023, remove duplicate rows first (one row per date), then compute the same rolling mean two ways: with ROWS BETWEEN 6 PRECEDING and with a calendar window ORDER BY julianday(date) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW. Report both values on 2023-03-13 and 2023-03-14, the first date of the 7 rows the ROWS window used on 2023-03-13, and the number of days present in the calendar window.
5. Write the guard you will add to any rolling or lag calculation on a feed that may have gaps.

Use flight_day, departures in 2023.

1. For each route, return the 5 departures with the most bookings_final, using ROW_NUMBER with a tie-break (date, then flight_no). Report the total row count.
2. Repeat with RANK() and with DENSE_RANK() and keep rows with rank at most 5. Report the total row count for each and, for each route, how many rows sit at the booking cap (bookings_final = CAST(capacity * 1.06 AS INT)).
3. For December 2023, return each route's SUM(bookings_final) and its percentage of the network total using a window over the grouped result (no subquery for the total). Add the rank of the route.
4. Repeat step 3 from the join of flight_day to booking_curve with no snapshot filter. State what changed and what did not.

In the first block, before running anything, write a table with one row per build task: the grain of the result, the number of rows you expect, and one control total or invariant it must reconcile to (for example, which table's sum, and for which period).

After the builds, add the observed values and the difference. Then write three guards, each as a SQL query that returns zero rows when everything is fine, that you will reuse:

• a grain guard for a join (the joined row count equals the left table's count);
• a window guard (every windowed value has the minimum number of days behind it);
• a tie guard (a top-N query returns exactly N rows per group, or the group is reported).

State which of your three builds you would trust least and why.