Core day 29 of 60 · Week 6 · Part I · evaluation depth

Senior DS/DA/MLE Case Day

A Monday message from the Head of Network Planning (simulated) reads: "IST-FRA is soft and our forecast keeps missing. Build me a better model before we lock the winter schedule." You have no data yet. What do you ask, in what order, and what is the smallest useful thing you can deliver?

By the end: You write five clarifying questions before seeing any data, pick the first analysis they justify, read its result with a stated uncertainty, and propose a scoped deliverable that says what is in and what is out.

At a glance

Turn a vague request into a decision, a metric and a first analysis, check the premise against a comparison group, and scope what you will and will not claim.

Symptom plus solution
Separate what is observed, what is proposed and the decision behind both.
Ask first
A good question changes what you do: decision, date, comparison, unit, what changed.
Check the premise
Size the symptom against a comparison group (other routes, stores or hospitals), with stated uncertainty.
Horizon match
A D-14 forecast is not evidence for a decision made five months ahead.
Scope
Deliver something narrow on time; name the exclusions and the evidence they need.

Recall · 5 min

From Day 1: name the six decision-first questions. From Day 28: for a what-if input, what are the three triage questions? Then clear your due review cards.

Open due reviews

Concept · 10 min

Real requests arrive as a symptom plus a solution: "the forecast keeps missing, build a better model". The symptom may be an artefact, the solution may not fix it, and the decision behind it may need a different object. A good question is one whose answer would change what you do; ask it before opening a notebook.

The verb in a request ("build a model", "fix the forecast") is a weak guide to the question that needs answering

Predict first

"Cut a weekly frequency on IST-FRA? Build me a model." Which kind of question is the cut?

Text description of the figure

Twelve airline request cards that you sort into describe or diagnose, predict, estimate, causal effect and decision; after sorting, each card reveals the method family, the validation it needs and its typical failure mode, and marks the cards whose wording points to a different type than the decision.

"IST-FRA is soft, build a better model": three things in one sentence Overview

The symptom
Forecast misses and soft bookings; may be real, an artefact, or a definition.
The proposed solution
A better model; may not touch the cause of the misses.
The decision behind it
Cut a winter frequency or not: a causal question with its own horizon.

Work the case in four moves, in order Sequence

Ask before data
Decision and owner, date, costs of each option, compared with what, metric and unit, what changed.
Check the premise
Is the symptom real and how large against a comparison group? Slice before you model.
Cheapest method that serves the decision
Diagnose, correct a baseline or rebuild; weigh data, time, auditability, serving.
Scope and name the exclusions
What you deliver by when, assumptions, a monitor, and what you will not claim.

Mean baseline error in seats, by route group and period (2024) Grid

Period of 2024 →
Route group ↓	Jan-Feb	Mar-Dec
Three stepped routes	−1.09	−2.07
Eight other routes	−0.45	2.37

Mean error per departure = actual minus the 2023 pickup baseline. The stepped routes' change minus the others' change is the difference-in-differences you compute in Stage 3.

Difference-in-differences Formula

\text{DiD} = (\bar e^{\,\text{after}}_{\text{stepped}} - \bar e^{\,\text{before}}_{\text{stepped}}) - (\bar e^{\,\text{after}}_{\text{other}} - \bar e^{\,\text{before}}_{\text{other}})

$\bar e$: mean baseline error in seats per departure
$\text{other}$: the comparison group: removes network-wide drift, if trends would have been parallel

It estimates the competitor step's effect on the baseline error, not the effect of cutting a frequency.

When the comparison group is a fair counterfactual, and when it is not Illustration

Parallel without the step: the dashed line (stepped routes had there been no step) moves with the other routes. DiD = -4.70 seats against a true -4.

Same step, but the stepped routes were already sliding 0.12 seats a week. DiD = -7.82: it adds the slide to the step, and the pre-period is too short to show it clearly.

Chance of detecting that 0.12-seat weekly slide from the pre-period gap (|t| > 2), by number of pre-period weeks. With eight weeks it is usually missed; it takes about half a year to see it reliably.

Synthetic weekly mean baseline errors (seed 2901): both groups share a network drift; the stepped group loses 4 seats per departure from week 9. Detection rates from 200 simulated pre-periods each.

The same step with and without a pre-existing drift, and how long a pre-period must be to see that drift.

A forecast's accuracy belongs to its horizon Illustration

Mean absolute error of the pickup forecast by days before departure. At D-14 it is 3.9 seats; at D-120, where the winter decision lives, it is 16.3, about the same as simply using the historical mean (dashed).

Why: the share of final bookings already on hand. Close to departure most of the answer is already booked; months ahead almost nothing is, so the pickup forecast has no information beyond history.

Synthetic: 600 departures (300 to estimate the average pickup, 300 to score), final bookings mean 150 and sd 20, a logistic booking curve with departure-level noise (seed 2930).

The pickup forecast is excellent near departure and no better than history months ahead, where the schedule decision is made. Score each candidate at the decision's horizon on rolling origins (Day 25), not where it looks best.

Ask before data: decision and owner, date, "soft compared with what", metric and unit, what changed.
Check the premise: is the symptom real, and how large against a comparison group, with stated uncertainty?
Cheapest method that serves the decision: diagnose, correct a baseline, or rebuild. A forecast at one horizon is not evidence for a decision at another.
Scope and name the exclusions: if the real question is causal, say so and name the evidence (Days 5 and 28).

Assumptions

The requester's words carry a real need; your job is to find it, not to dispute it.
The sample database is synthetic, with a known competitor step on three routes; a real case would need the same checks on actual market data.
Simulated stakeholder answers below stand for what a real conversation might yield; they are not facts about any airline.

Application and workplace decision · 10 min

Work the stages in order, writing each answer before reading on.

Stage 1 (no data): five clarifying questions, ranked.
Stage 2 (data released): run the query. It scores the D-14 pickup baseline (2023 average pickup by route and weekday) on every 2024 departure and compares the three routes whose competitor capacity stepped up 25% on 2024-03-01 with the other eight, before and after.
Stage 3: compute the Difference-in-differences (DiD) and say what it does not show.
Stage 4: write the deliverable you would commit to in two weeks, with exclusions.
Practice versions: S08 "IST-ADB is down 5%: cut fares 10%?" (/practice/scenario/scenario-017) and S03 "Three stakeholders and one overbooking limit" (/practice/scenario/scenario-005).

Your answer to the task (decision, method, baseline). Write it before reading the interpretation.

SQL (airline sample database)

WITH dep AS (
  SELECT f.date, f.route, f.weekday, f.bookings_final, b.cumulative_bookings AS bk_d14
  FROM flight_day AS f
  JOIN booking_curve AS b
    ON b.date = f.date AND b.flight_no = f.flight_no AND b.days_to_departure = 14
), pickup AS (            -- 2023 departures had all flown before any 2024 forecast was made
  SELECT route, weekday, AVG(bookings_final - bk_d14) AS pu
  FROM dep WHERE date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY route, weekday
), ev AS (
  SELECT d.*, d.bk_d14 + p.pu AS fc,
         CASE WHEN d.route IN ('IST-ADB', 'IST-AYT', 'IST-FRA')
              THEN 'competitor +25% from March' ELSE 'no competitor step' END AS grp,
         CASE WHEN d.date < '2024-03-01' THEN '1 Jan-Feb 2024' ELSE '2 Mar-Dec 2024' END AS period
  FROM dep AS d JOIN pickup AS p ON p.route = d.route AND p.weekday = d.weekday
  WHERE d.date >= '2024-01-01'
)
SELECT grp, period, COUNT(*) AS departures,
       ROUND(AVG(bookings_final - fc), 2) AS mean_error_seats,
       ROUND(AVG(bookings_final < fc), 3) AS share_below_forecast
FROM ev GROUP BY grp, period ORDER BY grp, period;

This code is executed in the repository's checks (pnpm lesson:py / lesson:sql). It is not run on this site; copy it into your notebook.

Technical depth · optional · 30 min in total

Open only what you need. Nothing here is required to complete the day; the assessment covers the core above.

Parallel trends and the 2x2, derivedadvanced5 minThe target (ATT) and the parallel-trends assumption written as equations, and the DiD derived from potential outcomes, placebo and regression form included.

Parallel trends is one equation about outcomes nobody observes Formula

\begin{aligned} &\mathbb E[Y_{\text{post}}(0) - Y_{\text{pre}}(0) \mid T = 1] \\ &\quad = \mathbb E[Y_{\text{post}}(0) - Y_{\text{pre}}(0) \mid T = 0] \end{aligned}

$Y(1),\ Y(0)$: potential outcomes with and without the change; after it each unit shows only one, and the treated group's Y(0) is never observed
$T = 1,\ T = 0$: the treated group (stepped routes, hospitals with a new programme) and the comparison group
$\text{target}$: ATT = E[Y_post(1) - Y_post(0) | T = 1], the effect on the units that were treated

The assumption is about changes, not levels: the groups may differ by a constant, but without the change they would have moved together. It is weaker than 'the groups are alike' and scale-dependent: parallel in seats need not mean parallel in log seats.

From four means to the effect, derived Sequence

Treated change
E[Y_post(1) - Y_pre(0) | T=1] = ATT + E[Y_post(0) - Y_pre(0) | T=1]: add and subtract the unseen Y_post(0).
Comparison change
E[Y_post(0) - Y_pre(0) | T=0]: the comparison group is never treated, so its change is a change in Y(0).
Subtract
DiD = ATT + (treated's untreated trend - comparison's trend). The bracket is the bias.
Assume
Parallel trends sets the bracket to zero, so DiD = ATT. Nothing in the four means can check it.
Placebo
Apply the formula to two periods before the change: ATT is 0 there, so the result estimates the bracket's past value.
Regression form
y = a + b T + c Post + d (T x Post) + e: d equals the DiD, and clustered SEs come for free.

Standard potential-outcomes algebra (see the Mixtape, ch. 9, and Roth et al., 2023). The same 2x2 sizes a store remodel, a tariff in some regions or a feature launched in some markets; Stage 2 runs it on baseline errors.

A 2x2 worked by hand, with a placebo and its standard erroradvanced7 minFour hospital means through the formula, a placebo, the SE of the difference from unit-level changes, the same steps on the lab's weekly series, and where it breaks.

Worked by hand: a discharge-coaching programme in 6 of 16 hospitals (illustrative) Table

Quantity	Programme hospitals (6)	Other hospitals (10)	Result
30-day readmissions, year before	12.0%	11.0%	levels differ by 1.0 point: allowed
30-day readmissions, programme year	10.5%	11.5%	-
Change	-1.5	+0.5	DiD = -1.5 - 0.5 = -2.0 points
Placebo: two years before -> year before	12.4 -> 12.0 (-0.4)	11.4 -> 11.0 (-0.4)	placebo DiD = 0.0: consistent with parallel trends
SD of each hospital's change	1.2	1.0	SE = sqrt(1.2^2/6 + 1.0^2/10) = 0.58
Interval (normal approx.)	-	-	-2.0 +/- 1.96 x 0.58 = -3.1 to -0.9 points

Regression check: y = 11.0 + 1.0 T + 0.5 Post - 2.0 (T x Post). The units are hospitals, not patients: thousands of discharges do not make 16 hospitals more independent. With 16 units a t quantile widens the band a little. Computed in Python.

The comparison group's change is the trend you subtract Bridge

Programme hospitals' change-1.5 points
Remove the shared trendthe other hospitals rose 0.5 points without the programme−0.5 points
DiD estimate of the ATT-2 points

Readmissions in the worked table: the programme hospitals fell 1.5 points, but the comparison group rose 0.5 over the same year, so the programme's estimated effect is -1.5 - 0.5 = -2.0 points, valid only under parallel trends.

The standard error of a difference-in-differences Formula

\begin{gathered} \mathrm{SE}(\widehat{\text{DiD}}) = \sqrt{\frac{s_T^2}{n_T} + \frac{s_C^2}{n_C}} \\ s = \text{SD of unit-level changes } (\bar y_{\text{post}} - \bar y_{\text{pre}}) \end{gathered}

$n_T,\ n_C$: independent units in each group: hospitals, stores, routes or weeks, never the rows inside them
$s_T,\ s_C$: how much the change varies from unit to unit; shared shocks inside a unit are already inside it

Under parallel trends the DiD is unbiased for the ATT; its variance falls with the number of independent units, not rows. Collapsing to one change per unit and comparing two means is the simplest honest version, and what clustering by unit does in the regression form.

Weekly gap in baseline error, stepped minus other routes (2024, hands-on lab) Chart

Weekly gap
Zero gap

Before March the gap averages -0.82 over 9 weeks and crosses zero: consistent with parallel trends, not proof of it. From March it averages -4.44 over 44 weeks and is negative every week. Insight-led title for the deliverable: the stepped routes fell behind from March.

The lab's weekly gaps as the units: estimate, SE and a placebo (computed) Table

Quantity	Rule	From the 53 weekly gaps above	Reading
Before March	mean and SD of 9 weekly gaps	-0.82, SD 1.41	the pre-period level of the gap
From March	mean and SD of 44 weekly gaps	-4.44, SD 2.12	the post-period level
DiD on weeks	after mean - before mean	-4.44 - (-0.82) = -3.62 seats	each week counts once, so it differs a little from the departure-weighted Stage 3 figure
SE, weeks independent	sqrt(1.41^2/9 + 2.12^2/44)	0.57: band -4.7 to -2.5	a floor: treats every week as a fresh draw
SE, weeks autocorrelated	lag-1 r = 0.61, n_eff = 44 x (1 - r)/(1 + r) = 11	0.80	neighbouring weeks repeat each other
Placebo inside the pre-period	mean of weeks 5-9 minus weeks 1-4	-1.46, SE 0.90	1.6 SE: not a clear pre-trend, not reassurance either

Same data as the hands-on lab, collapsed to one number per week. The point estimate survives every version; its band widens by 40% once neighbouring weeks are allowed to move together, and the short pre-period leaves room for a drift of about 1.5 seats. Computed in Python from the plotted series.

Three ways a DiD picks up something other than the effect Comparison

Criterion	Chosen because diverging	Another change that day	Spillover
What goes wrong	Treated units were already on another trend	Two changes share one date; the DiD takes both	The comparison group absorbs part of the effect
Example	A struggling store gets the remodel	A fare change on the day the competitor entered	Customers switch to the untreated store nearby
The check	Long pre-period plot and placebo dates	List what else changed on the date	A comparison group far enough away

Each failure adds a term to the parallel-trends bracket above. The six checks below turn these into a routine.

What the difference-in-differences can and cannot sayadvanced4 minWhy row counts overstate the evidence, shown on simulated years, and six checks before quoting the number.

Thousands of departures, far fewer independent observations Illustration

100 simulated years, each with 2,002 departures. The estimates (bars) spread with sd 1.17 seats; the row-level formula claims 0.57 (red bracket = its ±2 SE). Weekly gaps as units claim 0.98 (green).

How often a nominal 95% interval contains the true effect across the simulated years. Counting departures as independent overstates the information; treating weeks as the units comes much closer.

Synthetic: 3 stepped and 8 other routes, 182 days, step on day 63 with a true effect of -4 seats; route errors persist from day to day and routes in a group share daily shocks (seed 2929).

Simulated years show how widely the estimate really moves, against what a row-level and a week-level interval claim.

Six checks before quoting a difference-in-differences Sequence

Plot the pre-period
Weekly gaps before the change; a drift there is added to the estimate.
Placebo date
Pretend the change happened earlier; a large effect there means the design is picking up something else.
Placebo group
Run it on units that had no change (routes, stores, hospitals); it should be near zero.
Count the right units
Weeks or routes, not departures: shared shocks and persistence shrink the information.
Other changes on the date
Fares, schedule or data changes on the same day break the attribution.
Window sensitivity
Shorter and longer after-periods should tell the same story.

Background on the method and its assumptions in the Mixtape (ch. 9) and Roth et al. (2023).

Stage 1 questions, ranked2 minFive questions and the part of the problem each answer fixes.

Five Stage 1 questions and what each answer fixes Table

Question	What the answer fixes
What is decided, by whom, and by when?	The horizon, the grain and the deadline
Soft compared with what: last year, plan, other routes?	The comparison group and the metric
What does 'keeps missing' mean, in which unit?	The baseline and the error measure
What changed, and when?	Breaks, data issues and a candidate cause (Day 21)
What would make you stop or change course?	The decision threshold and what evidence matters

Rank them by how much each answer would change your approach.

The verb in a request is a weak guide to its type Overview

Describe or diagnose
What happened and where? Slices and a comparison group.
Predict
What will happen under current policy? A forecast with a band.
Estimate
How large is a quantity, with uncertainty?
Causal effect
What if we changed something? Needs a design (Days 5 and 28).
Decision
What should we do, given costs and evidence?

Checking the premise before modelling2 minThree cheap checks that can end a case before any model is built.

Is the symptom real? Check before you model Diagram

Definition changed?metric or source
- yes: to Fix the definition
no
Data gap or late rows?
- yes: to Fix the pipeline
no
Larger than comparison?vs other routes
- no: to Network-wide: drift
yes
A local changedate it, size it with uncertainty, find the driver
Fix the definitionrestate the history
Fix the pipelinethen re-measure
Network-wide: driftupdate the baseline, no route story

Each diamond is cheaper than a model and can end the case on its own.

Matching the instrument to the decision2 minWhy the D-14 baseline diagnoses the soft quarter but cannot forecast winter.

The instrument and the decision live at different horizons Diagram

D-14 pickup baselineneeds bookings on hand
- serves: to Diagnosis of the misses
not evidence
Winter schedule decision15 October, 2-5 months ahead
- needs: to Months-ahead forecast
Diagnosis of the missesdates and sizes them
Months-ahead forecastcalendar, capacity, rivals
plus
Evidence for the cuta matched comparison (Day 28)

Months ahead, bookings on hand barely exist, so the D-14 baseline has nothing to read; a months-ahead forecast leans on calendar, history, known capacity and competitor schedules. A forecast built at one horizon is not evidence for a decision at another.

Three role lenses on one case2 minWhat the analyst, data scientist and ML engineer each own.

Who owns which part of the case Comparison

Criterion	Analyst	Data scientist	ML engineer
Owns	The premise check and the chart	The method and its evidence	Where the number runs
Delivers	Slices, the weekly gap, an insight-led title	Corrected baseline, band, DiD with limits	Serving, the bias monitor, a fallback
Checks	Definitions and data coverage	Assumptions such as parallel trends	What happens when it is wrong

A senior covers all three on one page.

Writing the scope2 minWhat goes in the two-week deliverable, and what stays out with a reason.

The two-week deliverable Comparison

Criterion	In scope	Out of scope (and why)
The question	One-page diagnosis with the weekly gap chart	The effect of cutting a frequency: not estimated by these data
The forecast	Baseline with a route bias term, known-future competitor capacity, a band	A new model class: not needed to answer the decision
Uncertainty	A bias monitor with an alert threshold	A tight interval for the competitor effect: dependent departures, two pre-March months

Working the stages in order2 minWhy each stage is written down before the next one is read.

The staged case Diagram

Stage 1: questionsno data yet
data released
Stage 2: databaseline error by group and period
compute DiD
Stage 3: judgementDiD and what it does not show
commit
Stage 4: scopein, out, and the evidence for out

Write each answer before reading on; that exposes how much the data changed your view, which is the point of a staged case. The practice scenarios S08 and S03 extend the same structure.

What a staff-level answer addsadvanced2 minFive habits that turn a vague request into a decision-useful deliverable.

What a staff-level answer adds Overview

Ask for the decision date
It fixes the horizon, which decides which forecast is even relevant.
Kill the premise cheaply
Definition, data and a comparison group before any model.
Units
Quote uncertainty from weeks or routes; say how many there are.
Write the exclusions
What the number does not show, and the evidence that would show it.
Time-box the partial answer
Deliver the narrow useful thing by the date; schedule the rest.

Memory anchor

Ask what would change your approach, check the premise against a comparison, ship the narrow useful answer, and write down what it does not show.

Useful for

Triage of any ambiguous request in the first hour ("churn is up, build a model", "claims are spiking", "the line keeps stopping"), before data access or modelling.
Writing the exclusions in a scoped deliverable so that a forecast is not mistaken for an intervention estimate.
Rehearsing the case and stakeholder rounds of senior interviews.

Not suitable for

Well-specified tasks where the decision, metric and data are agreed; the extra round of questions is cost without information.
Settling the causal question itself - the case ends with the evidence you would collect, not with an effect size.

Traps

Starting from the data you have

The notebook opens before the decision is known: a churn model is built at a 7-day horizon for a pricing decision a quarter out, or a D-14 pickup model for a schedule five months out, and its low error is quoted as support.

How to avoid it

Write the decision, owner, date and horizon first; name the object that serves them.

Turning a clean comparison into a causal answer

A -3.8 seat difference-in-differences becomes "cutting a frequency loses 3.8 seats". The comparison measured the baseline's miss under the existing schedule.

How to avoid it

State the intervention the number measures and the one the stakeholder asks about; name the gap.

Senior reasoning

A senior spends the first minutes on the decision and the comparison, not on the model. They rank questions by what an answer would change, run the cheapest check that could kill the premise, and describe the deliverable in terms of what the requester can do with it by the date. They write the exclusions as carefully as the inclusions, because the exclusions are what stop a forecast from being used as an intervention estimate.

Interview question

"Your stakeholder says the forecast is wrong and asks for a better model by next week. Walk me through the first hour." How do you structure it?

Outline your answer first.

Recall cards scheduled after completion

Name the four moves of the case routine and the first output of each.
Why is a difference-in-differences of forecast errors not the effect of a business action?

Reviewed on a 1 · 3 · 7 · 14 · 30-day schedule; a miss returns the card to 1 day.

Prerequisites and related concepts

References

Causal Inference: What If — Miguel A. Hernán, James M. Robins, 2020optional
Distinguishing a comparison from an intervention effect, and the assumptions a difference-in-differences relies on. (Part I, ch. 7-8)
Causal Inference: The Mixtape — Scott Cunningham, 2021optional
Difference-in-differences with its parallel-trends assumption. (Ch. 9 Difference-in-Differences)
What's Trending in Difference-in-Differences? A Synthesis of the Recent Econometrics Literature — Jonathan Roth, Pedro H. C. Sant'Anna, Alyssa Bilinski, John Poe, 2023optional
What recent work says about when two-group difference-in-differences is credible and how to check it.

Optional sections

Deep dive · 10 min

A question bank by role. An analyst asks: how is the metric defined, which rows are in or out, did the source change, and what is the comparison. A data scientist asks: what is the target and grain, what is known at decision time, which baseline, what error costs what, and which validation matches the deployment. An ML engineer asks: who consumes the number, how often, at what latency, who is paged when it is wrong, and how it is rolled back. Pick the five that would change your plan.

Why the baseline's "miss" has two parts. The 2023-based pickup does not include the network's growth (the simulator has about 5% a year of trend), so the unaffected routes drift upward by 2.8 seats; the stepped routes drift less. A report that only looks at FRA's error sees a smaller miss than the effect relative to the network, and one that only looks at the network sees growth. The comparison group is what separates them.

Pre-trends and the weekly series. Difference-in-differences assumes the groups would have moved in parallel. With two pre-period months you can only look at the weekly gaps: if they were already drifting apart, the estimate inherits that drift. The lab prints the weekly summary; extend it to a series and look.

Dependence. Departures on the same date share demand shocks across routes, and consecutive days are autocorrelated, so a standard error computed as if the nearly 7,000 departures were independent is far too small. Block the data by week and treat the weekly gaps as the observations, as the lab does, or use clustered errors.

From diagnosis to deliverable. The corrected baseline is the pickup model plus a recent-error bias term: bias for each route is the mean error on the latest flown weeks (known at the cutoff), with competitor capacity as a known-future input. It is cheap, auditable and quickly replaced if a better model wins on the same rolling origins (Days 25-27). The monitor tracks the bias per route and flags a sustained drift.

What would answer the causal question. A matched comparison of departures where the frequency differs for reasons unrelated to demand (schedule changes forced by slots or aircraft availability), a staged pilot on one day of week with a control, or a structural model with demand elasticity from an experiment. Each has costs and assumptions; none comes from the forecast.

Hands-on lab: The weekly series under the difference-in-differences · 15 min

Run the query. It computes, for each calendar week of 2024 (the week of 26 February holds three days after the step and is counted as before March), the gap in mean baseline error between the three stepped routes and the other eight, and summarizes the weekly gaps before March and from March. Read three things: the mean gap in each period, whether the weekly gaps overlap between periods, and the share of weeks with a negative gap. Then (1) edit the last SELECT to return wk, wk_start and gap per week and chart it, with a vertical marker on 2024-03-01 and an insight-led title; (2) state in one sentence what the pre-March weeks suggest about the parallel-trends assumption; (3) write what you would claim and what you would not claim in the deliverable.

WITH dep AS (
  SELECT f.date, f.route, f.weekday, f.bookings_final, b.cumulative_bookings AS bk_d14
  FROM flight_day AS f
  JOIN booking_curve AS b
    ON b.date = f.date AND b.flight_no = f.flight_no AND b.days_to_departure = 14
), pickup AS (
  SELECT route, weekday, AVG(bookings_final - bk_d14) AS pu
  FROM dep WHERE date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY route, weekday
), ev AS (
  SELECT d.date, d.bookings_final - (d.bk_d14 + p.pu) AS err,
         d.route IN ('IST-ADB', 'IST-AYT', 'IST-FRA') AS stepped
  FROM dep AS d JOIN pickup AS p ON p.route = d.route AND p.weekday = d.weekday
  WHERE d.date >= '2024-01-01'
), wk AS (
  SELECT strftime('%Y-%W', date) AS wk, MIN(date) AS wk_start,
         AVG(CASE WHEN stepped = 1 THEN err END) - AVG(CASE WHEN stepped = 0 THEN err END) AS gap
  FROM ev GROUP BY 1
)
SELECT CASE WHEN wk_start < '2024-03-01' THEN '1 before March' ELSE '2 from March' END AS period,
       COUNT(*) AS weeks,
       ROUND(AVG(gap), 2) AS mean_weekly_gap,
       ROUND(MIN(gap), 2) AS min_gap,
       ROUND(MAX(gap), 2) AS max_gap,
       ROUND(AVG(gap < 0), 2) AS share_weeks_negative
FROM wk GROUP BY 1 ORDER BY 1;

Interview practice · 10 min

A VP says "conversion is down and the model we shipped last quarter must be the reason - turn it off by Friday." You have not seen any data. How do you respond?

Answer as you would in the interview.

Content version 6 · last verified 2026-10-18 · depends on sqlite

1 Recall · 5 min

2 Concept · 10 min