Core day 24 of 60 · Week 5 · Part I · application depth

ML Forecasting

You want a gradient-boosting model to forecast departure bookings 14 days ahead for every route. Which inputs may it use, how do you build lag and rolling features so that the value in training is the value available at the cutoff, and which of the drivers the business proposes will not exist for the whole horizon?

By the end: For a chosen horizon you write one point-in-time-safe lag and rolling feature per entity, classify each proposed driver as future-known or future-unknown, and show on a time-ordered split what a leaky window does to the error.

At a glance

Forecasting becomes regression on a table whose every column must exist at the cutoff, built per entity and shifted by the horizon.

A table
Rows are entity and target time; columns are what you could know at the cutoff t-h.
Shift
Shift by the horizon inside each entity (store, meter, route) before any rolling window.
Known or unknown
Calendar and planned promotions are known ahead; a rival's price and the weather are not.
Strategy
Recursive feeds predictions back; direct fits one model per horizon.
The leak test
Ask of every window where it ends; later than the cutoff means a forecast nobody can make.

Recall · 5 min

Without looking back: state the availability test from Day 2 for a column at a cutoff, and say why a tree model cannot forecast a value above the largest target it saw in training (Day 12).

Open due reviews

Concept · 10 min

A machine-learning forecaster does not know about time. You hand it a table: one row per entity and target time t, columns that exist at the cutoff t-h, and the value at t as the label. All time-awareness lives in the columns you build, so the cutoff is the central object: for a 14-day forecast the newest usable value is from t-14.

A rolling window that reaches the target makes validation look better and the deployed model worse

Predict first

With the defaults (seasonal series, 3 lags, window 3, horizon 2, leak on), how does the leaky validation error compare with the honest one, and what happens to the leaky model once deployed?

Text description of the figure

A selector for a seeded series (seasonal with period 12, or AR(2)), sliders for the number of lags, the rolling window and the horizon, and a toggle that lets the window include the current value. One chart marks the target, the lag values it reads, the shaded window and the cutoff; a table shows the supervised rows; a readout gives the validation error with safe features, with a leaky window and the leaky model once deployed. A second chart shows backtest error by horizon step for direct and recursive strategies.

Five ideas that make ML forecasting honest Overview

A table
Each row: one entity (store-SKU, meter, route), one target day; label = its value.
Shift by the horizon
For a 14-day forecast the newest usable value is from t-14.
Per entity
Shift and roll inside each entity, or store B reads store A's last days.
Known versus unknown
Only drivers known at the cutoff for the whole horizon enter as they are.
A strategy
Recursive, direct or multi-output: chosen by backtest.

Where every window must end, for a forecast made 14 days ahead Diagram

Day t-14 and earlierlags t-14, t-21; 7-day window t-20 to t-14
Cutoff: t-14the forecast is made now
Days t-13 to t-1not yet observed at the cutoff
Target day tlabel; known-ahead drivers align here

In SQL the safe frame is ROWS BETWEEN 20 PRECEDING AND 14 PRECEDING. A window that reaches into the red block scores well offline and does not exist in production.

Where each window ends, and what that does to the score Illustration

One training row, target day t, forecast made at t - 14. Green cells are readable at the cutoff; red cells are not. The holiday flag sits on day t but was planned in advance; the competitor fare on day t was not.

For h of 2 or more the leaky validation error is 37-49% of the honest one (at h = 1 it is 0.00: the window plus the lags rebuild the target exactly). Deployed with the window that exists at the cutoff, the same model scores 2.5 times the honest error on average.

Left: feature definitions from the course's lag-feature code applied to day indices. Right: a seeded seasonal teaching series (240 points, 3 lags, a 3-point window), linear fit on points before 160, validation after; MAE in series units.

Left, the days each feature of one row reads, computed by the course's lag-feature code. Right, honest, leaky and deployed errors of the same features at several horizons.

Future-known versus future-unknown drivers Comparison

Criterion	Future-known	Future-unknown
Examples	Calendar, holidays, timetables, maintenance plans, planned price or promotion	A rival's price, weather, a competitor's capacity move, a viral post
How to use it	Align to the target row; check it cannot still change	Lag it to the cutoff, forecast it, or fix it as a scenario
If scored with realised values	Fine, if the plan really was final at the cutoff	Ex-post forecast: overstates what you can do

Three multi-step strategies Comparison

Criterion	Recursive	Direct	Multi-output
Models	One one-step model	One model per horizon	One model, all horizons at once
Inputs at horizon h	Its own predictions fed back as lags	Only real values at least h steps old	Real values up to the cutoff
Error behaviour	Errors compound with the horizon	No feedback; neighbouring horizons may zigzag: smooth or reconcile them	No feedback; shared structure
Cost	One model to train	H models: the rows x columns cost of boosting, H times	One larger model

No strategy wins everywhere; choose by backtest at the horizons the decision uses (Day 25). Direct's H-fold bill is the practical reason 28-day daily plans (retail replenishment, call-centre staffing) often go recursive or multi-output. Hybrids, a direct model per block of horizons, sit in between; the registered NN5 study is one data point, not a rule.

Three scores, one of them real (today's run, 14 days ahead) Chart

Seasonal naivethe value 14 days earlier27.4 MAE
Window ends at the cutoffthe honest number19.4 MAE
Leaky window, validationlooks 31% better13.4 MAE
Leaky model, deployedworse than honest20.3 MAE

Synthetic panel of five routes, chronological split, one global LightGBM model.

Lags and rolling windows: shift by the horizon within each entity (route, store, meter), then roll.
Drivers: future-known (calendar, timetable, planned price or promotion) or future-unknown (a rival's price, weather); score unknown ones only with values that existed at the cutoff.
Strategies: recursive, direct or multi-output; choose by backtest at the decision horizon.
Global model: one model across all entities, with inputs scaled and detrended before the cutoff.

Assumptions

Each row's features come from timestamps at or before its own cutoff, in training and in production, with the same definition and missing-data handling.
The feature-target relation is stable enough for a model fitted on the past to describe the evaluation window; regime changes need explicit variables (Day 21).
Future-known drivers carry their final value at forecast time; a schedule or holiday that can still change is uncertain too.

Application and workplace decision · 10 min

The code builds a synthetic panel of five routes over 420 days and forecasts 14 days ahead with one LightGBM model for all routes. Before running it, predict which of the three scores production will show.

Data: weekly shape, a 10-day holiday window every 60 days (known in advance), persistent demand shocks.
Split: train on days before 330, test on the last 90 days.
Next to lag-14 and lag-21: a 7-day mean that ends at the cutoff, one that ends at t, and the leaky model scored with the feature that exists at the cutoff.

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

Python

import numpy as np
import pandas as pd
from lightgbm import LGBMRegressor
from scipy.signal import lfilter

rng = np.random.default_rng(24)
h, n, routes = 14, 420, ["A", "B", "C", "D", "E"]       # forecast made 14 days ahead; 5 synthetic routes
week = np.array([1.00, 0.95, 0.93, 0.97, 1.08, 1.12, 1.05])
rows = []
for i, r in enumerate(routes):
    d = np.arange(n)
    shock = lfilter([1], [1, -0.8], rng.normal(0, 0.05, n))          # persistent demand shock (AR(1))
    hol = ((d % 60) >= 50).astype(int)                               # a 10-day holiday window every 60 days (known)
    y = (100 + 25 * i) * (1 + 0.0005 * d) * week[d % 7] * (1 + 0.25 * hol) * np.exp(shock)
    rows.append(pd.DataFrame({"route": r, "day": d, "dow": d % 7, "hol": hol, "y": y}))
df = pd.concat(rows, ignore_index=True)

g = df.groupby("route")["y"]
df["lag_h"] = g.shift(h)                                              # newest value known at the cutoff
df["lag_h7"] = g.shift(h + 7)
df["roll7_ok"] = g.transform(lambda s: s.shift(h).rolling(7).mean())  # window ends at the cutoff t - h
df["roll7_leak"] = g.transform(lambda s: s.rolling(7).mean())         # window ends at t: contains the target
df = df.dropna()

train, test = df[df["day"] < 330], df[df["day"] >= 330]
base = ["dow", "hol", "lag_h", "lag_h7"]
def fit_score(col, score_col):
    m = LGBMRegressor(n_estimators=300, learning_rate=0.05, num_leaves=15, verbose=-1, random_state=0)
    m.fit(train[base + [col]], train["y"])
    return np.abs(test["y"] - m.predict(test[base + [score_col]])).mean()

print(f"seasonal naive (y[t-{h}]): MAE {np.abs(test['y'] - test['lag_h']).mean():.1f}")
print(f"window ends at cutoff:     MAE {fit_score('roll7_ok', 'roll7_ok'):.1f}")
print(f"window includes target:    MAE {fit_score('roll7_leak', 'roll7_leak'):.1f} (validation) -> "
      f"{fit_score('roll7_leak', 'roll7_ok'):.1f} when deployed")

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 · 31 min in total

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

The supervised table as a design matrix5 minWhat one row and one column mean in symbols, how many rows a horizon leaves, and why an AR(p) and a GBM forecaster are the same table with different f.

From a series to a supervised table Diagram

Series per routedaily, on a full grid (Day 6)
Pick target day tlabel = y(t)
Features up to t-hlags, windows, calendar, plans
One modelall routes, chronological split
Forecastfrom the cutoff, h days ahead

Stack every entity's rows: that matrix X and label vector y are the whole learning problem. Neighbouring rows share most of their lag values, so they are far from independent: shuffled cross-validation on X is optimistic (Day 22's effective sample size, Day 25's folds).

The supervised table is a design matrix: one row per target, lags at least h old Formula

\underbrace{y_{i,t}}_{\text{label}} \approx f_h\big(\underbrace{y_{i,t-h},\dots,y_{i,t-h-p+1}}_{\text{lags} \,\ge\, h},\ \underbrace{c_{i,t}}_{\text{known ahead}}\big)

$i,\ t$: entity (store-SKU, meter, route) and target time: each pair is one row of the matrix X
$y_{i,t-h}$: the newest lag allowed for horizon h; p lags give p columns, so row t needs t - h - p + 1 >= 1
$c_{i,t}$: calendar and planned drivers, stamped at the target time because they are known in advance
$f_h$: any regressor: linear f on lag columns is an AR(p) fitted by least squares; a GBM is the same table, non-linear f
$\hat f_h$: argmin over f of the summed loss L(y_(i,t), f(x_(i,t))): squared error targets the conditional mean, absolute error the median, pinball a quantile (Day 16)

Rows per entity: T - p - h + 1. With 500 stores, 3 years of days, p = 28 and h = 14 that is about 500 x 1,054 = 527,000 rows; a direct strategy builds one such matrix per horizon. With one entity, squared loss and h = 1 this is Day 23's AR(p); calendar columns make it a linear regression on lagged and known-ahead inputs.

One training row: route A, target day 100, horizon 14 Table

Feature	Days it reads	Safe?
lag_h	day 86	Yes: newest value known at the cutoff
lag_h7	day 79	Yes
roll7_ok (shift 14, then roll 7)	days 80-86	Yes
roll7_leak (roll 7, no shift)	days 94-100	No: contains the target day
hol (holiday window)	day 100	Yes: planned in advance, aligned to the target
competitor fare, same day	day 100	No: unknown at the cutoff unless forecast

Recursive versus direct: two estimands, worked at h = 1 and h = 2advanced6 minEight load readings pushed through both design matrices by hand, the limit each slope converges to, and a computed world where the difference costs accuracy.

Worked by hand: eight days of a feeder's load deviation as two design matrices Table

Day t	x_t (MWh)	h = 1 input x_(t-1)	x_(t-1) x_t	h = 2 input x_(t-2)	x_(t-2) x_t
1	4	-	-	-	-
2	3	4	12	-	-
3	1	3	3	4	4
4	2	1	2	3	6
5	-1	2	-2	1	-1
6	-2	-1	2	2	-4
7	-1	-2	2	-1	1
8	1	-1	-1	-2	-2
Sums	-	squares: 36	18	squares: 35	4
Slope (no intercept)	-	phi_1 = 18 / 36 = 0.50	7 rows	beta_2 = 4 / 35 = 0.114	6 rows

Illustrative deviations of daily energy load from the weekday norm; the norm is the model's mean, so the slopes are fitted without an intercept. Each horizon drops rows at the start: 7 usable rows for h = 1, 6 for h = 2. numpy least squares returns 0.500 and 0.1143.

Recursive versus direct, in symbols Formula

\text{recursive: } \hat y_{t+2} = f(\hat y_{t+1}, y_t, \dots) \qquad \text{direct: } \hat y_{t+h} = f_h(y_t, y_{t-1}, \dots)

$f$: one one-step model, applied repeatedly to its own output
$f_h$: a separate model for each horizon h, trained only on real lags at least h old

The recursive path feeds a prediction into the next step, so a bias at step 1 travels to every later step.

Recursive and direct estimate different things at h = 2 Formula

\text{recursive: } \hat y_{t+2} = \hat\phi_1^{\,2}\, y_t \ \to\ \rho_1^{2}\, y_t \qquad \text{direct: } \hat y_{t+2} = \hat\beta_2\, y_t \ \to\ \rho_2\, y_t

$\hat\phi_1$: least-squares slope of y_(t+1) on y_t; it converges to rho_1, the lag-1 autocorrelation
$\hat\beta_2$: least-squares slope of y_(t+2) on y_t, fitted on its own rows; it converges to rho_2
$\rho_2 = \rho_1^2$: true for an AR(1): then both target the same number and recursive, which uses more rows and the model's structure, is less noisy
$\rho_2 \ne \rho_1^2$: any other process: recursive targets a power of a misspecified one-step model; direct still targets the best linear 2-step predictor
$\mathbb{E}[f(Y)] \ne f(\mathbb{E}[Y])$: for a non-linear f (trees, nets) feeding back a point forecast is biased even if f is right; direct asks the h-step question directly

The worked table: recursive says 0.5^2 x 1 = 0.25 for day 10, direct says 0.114 x 1 = 0.11 - the same data, two estimands. Classic trade-off: recursive risks bias when the one-step model is wrong, direct pays in variance and in H separate fits.

When the one-step model is wrong, recursive and direct part ways Illustration

True process AR(1), phi = 0.7. One-step fit phi_hat = 0.720; recursive gives phi_hat^h, direct fits each h separately. Both estimate the same target phi^h (black), so here direct only adds estimation noise.

True process AR(2) (0.3, 0.5), but both strategies see only x_t. One-step slope 0.63, so recursive implies 0.40 at h = 2; the real lag-2 dependence is 0.68 and direct estimates 0.70.

Same AR(2) world, 4000 test points. At h = 1 the strategies are one model. From h = 2 on direct has the lower MSE, by up to 11% (h = 2): it targets the h-step conditional mean given x_t, recursive targets a power of the wrong one-step model.

true best linear h-step coefficient (rho_h)
recursive: one-step slope to the power h
direct: least-squares slope fitted per horizon

Computed: seeded Gaussian AR(1) (phi 0.7) and AR(2) (phi 0.3, 0.5) series, 4000 training and 4000 test points each, mean zero, so slopes are fitted without an intercept. Synthetic data; real series sit between these two worlds, which is why the strategy is chosen by backtest.

Coefficient on the origin value by horizon in a world where an AR(1) is right (left) and one where it is not (middle), and the test MSE that follows (right). Both strategies see the same single input. Real cases of the middle world: a weekly echo in parcel volumes, a two-day heat wave in load, a payday pattern in card spend.

Building the table safely in pandas and SQL3 minThe same safe feature in pandas and SQL, and the airline sample database showing what the leaky frame does.

The same safe feature in pandas and SQL Table

Feature	pandas	SQL window frame
Lag at the horizon	g.shift(14)	LAG(y, 14) OVER (PARTITION BY route ORDER BY date)
7-day mean at the cutoff	g.transform(lambda s: s.shift(14).rolling(7).mean())	AVG(y) OVER (... ROWS BETWEEN 20 PRECEDING AND 14 PRECEDING)
The leak	s.rolling(7).mean() or center=True	ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

g = df.groupby('route')['y']. pandas rolling windows are trailing by default; center=True pulls later values in.

The same leak in SQL on the airline database (hands-on lab) Chart

Seasonal naive, 14 rows back28.8 MAE seats
7-row mean ending at the cutoff23.3 MAE seats
7-row mean ending at tcontains the target: not available19.8 MAE seats

All 2024 departures (6,954) in the synthetic sample database.

Four leaks that look like skill2 minThe patterns behind most too-good forecasting backtests, and a test that catches them automatically.

Four leaks that look like skill Overview

Window ends at t
The rolling mean contains the target.
Shift ignores the entity
Store B's first rows read store A's last values.
Centred window
center=True pulls later values into today's row.
Realised unknown driver
Same-day rival price or realised weather scored as if it were known.

A chronological split does not expose the first three by itself: today's leaky window still scored 13.4 on later days. Rebuild every feature exactly as production will, then rescore.

A feature-availability test to put in CI Sequence

Stamp every feature
Store the latest source timestamp each feature value read.
Compare with the cutoff
Assert stamp <= t - h for every row, per entity.
Recompute at serving time
Build one row the way production will and diff it with the training row.
Shuffle-the-future check
Permute values after each cutoff; scores must not change.
Fail loudly
A violated assertion blocks the training run, not just a dashboard.

Practitioner heuristic. The shuffle check catches leaks that a code review misses, because the score itself reveals them.

Drivers and their availability3 minA decision rule for each proposed driver, and why price needs special care.

What to do with a proposed driver Diagram

Known for the whole horizon?at the cutoff
- yes: to Align to the target row
no
Can you forecast it?
- yes: to Forecast it
no
Use its value at the cutoffor fix it as a named scenario
Align to the target rowcalendar, timetable, promotion plan
Forecast itscore with forecasts that existed then

Recursive, direct and multi-output in practice3 minThe same AR(3) features iterated or refitted per step on rolling origins.

Recursive and direct, computed Illustration

Recursive: one one-step model, its own forecasts fed back as lags. Direct: a separate model for each step, fed only observed values. Both start from the same last three observations.

At step 1 the two strategies are the same model. From step 4 on they part: at step 12 the recursive MAE is 1.37 against 0.98 for direct, because errors made early are fed back into every later step.

recursive (one AR(3), iterated)
direct (one AR(3) per step)

Seeded seasonal teaching series (period 12, AR noise; 240 points). Linear AR(3) models refitted at 25 origins between 150 and 228. This series favours direct; on others the order can flip, so choose by backtest.

The same AR(3) features, iterated (recursive) or refitted per step (direct), on 25 rolling origins. Early errors feed every later recursive step.

Global models across many seriesadvanced3 minScaling, detrending and entity-safe features for one model across routes.

Making one global model work across many series Sequence

Same grid for every route
Full calendar per route; missing is not zero (Day 6).
Scale before the cutoff
Divide by each series' level (store, meter, route) computed only from data before the cutoff.
Detrend or difference
Trees predict constants inside the range they saw (Day 12): model a ratio or a pickup, not the level.
Entity-safe features
Group by entity for every shift and window; add the entity id as a feature.
Chronological split
Train on earlier days for all entities, test on later days (Day 25).

The payoff is shared shape. Seasonal and holiday patterns are learned across series, so a newly opened store or a new smart-meter customer borrows them from day one.

Feature families3 minThe six families of forecasting features and the leak each one invites.

Six feature families and the leak each one invites Overview

Lags
y at t-h and older; seasonal lags at period multiples (14, 21, 28 for h = 14). Leak: a lag shorter than h.
Rolling statistics
Mean, sd, max, share of zeros: each needs the same shift. Leak: a window ending after t-h.
Expanding and EWM
All history with equal or fading weights. Leak: no shift by h.
Calendar
Weekday, day of year, holiday window. Safe: known in advance.
Entity encodings
Store, SKU or route mean; target encoding. Leak: computed on the whole history.
Planned drivers
Price list, promotion calendar, capacity. Leak: the plan as revised after the cutoff.

Gradient boosting knobs for forecastingadvanced3 minLightGBM defaults, the loss to choose for the decision, and the capacity knobs that matter on small panels.

LightGBM knobs that matter for forecasting (defaults from lightgbm 4.7.0) Table

Knob	Default	Forecasting note
objective	None, which LGBMRegressor treats as 'regression' (squared error)	'l1' targets the median, 'quantile' with alpha a quantile, 'tweedie' many zeros
num_leaves	31	Main capacity knob; small panels overfit fast with leaf-wise growth
min_child_samples	20	Raise it so a leaf cannot memorise a single holiday week
learning_rate / n_estimators	0.1 / 100	Lower rate, more rounds, stop on a later time block
entity id as category	'auto': pandas category columns	Lets one global model learn store, meter or route levels; still scale per entity

Defaults read from LGBMRegressor().get_params(). Pick the loss from the decision: squared error for the mean, absolute error for the median, quantile loss for a newsvendor's stock level. Early stopping must use a later time block, never a random split of the training rows.

Memory anchor

Shift by the horizon, per entity, before you roll - and ask of every column when its last value could have existed.

Useful for

Many related series (routes, stores, SKUs) where one global model can borrow strength and covariates such as price and events are available.
Demand with holidays, promotions and events that are known in advance and that a model with calendar features can use directly.
Building a feature table that serves the same values in training and in production with a stated horizon.

Not suitable for

A single short series with no covariates - ETS or ARIMA (Day 23) are cheaper and often as accurate.
Answering a price or capacity what-if from the fitted model - prediction with confounded inputs is not an intervention (Days 5 and 28).

Traps

A rolling window without a shift

The default pandas window ends at the current row, so the feature contains the target. Random or even chronological validation rewards the model for a value that does not exist at the cutoff.

How to avoid it

Shift by at least the horizon per entity before rolling, and write a test that checks every feature's latest timestamp against the cutoff.

Scoring with realised future-unknown drivers

A rival's price or the weather on the target day is in the history table, so the backtest uses it. In production you have only a forecast of it, and the accuracy you reported is for an ex-post forecast.

How to avoid it

Keep archived vintages of each unknown driver, or evaluate with the forecast that existed at the cutoff, and say whether results are ex-ante.

Senior reasoning

A senior designs the feature table backwards from the decision: horizon, cutoff, and for each column the last timestamp it can have. They keep the baseline in every comparison, prefer a direct model with lags of at least h unless a backtest says otherwise, and treat anything computed with a window or a scaler as a place to look for a leak. They also refuse to read price coefficients as elasticities from data where prices responded to demand.

Interview question

"How do you build lag and rolling features for a 14-day-ahead demand forecast across 500 stores without leaking?" Structure the answer.

Outline your answer first.

Recall cards scheduled after completion

Write the safe rolling-mean feature for horizon h per entity, and what goes wrong without the shift or the groupby.
Which drivers are future-known, how do you treat future-unknown ones, and how do direct and recursive forecasts differ?

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

Prerequisites and related concepts

References

Windowing operations (pandas User Guide) — pandas development teamcore
Rolling windows are trailing by default and the center option moves labels to the middle of the window; expanding windows accumulate all values up to each point. (Window operations - rolling and expanding windows)
Forecasting: Principles and Practice (3rd edition) — Rob J Hyndman, George Athanasopoulos, 2021core
Calendar variables and intervention (spike and step) predictors; ex-ante versus ex-post forecasts and why predictors must themselves be forecast or assumed. (Sections 7.4 and 7.6)
A review and comparison of strategies for multi-step ahead time series forecasting based on the NN5 forecasting competition — Souhaib Ben Taieb, Gianluca Bontempi, Amir F. Atiya, Antti Sorjamaa, 2012optional
Review of direct, recursive and multiple-output strategies on 111 NN5 series, where multiple-output strategies performed best and deseasonalisation helped; the result is specific to that competition.
Principles and algorithms for forecasting groups of time series: Locality and globality — Pablo Montero-Manso, Rob J. Hyndman, 2021optional
Global methods are not more restrictive than local ones and keep constant complexity as the number of series grows.
MLForecast documentation (Nixtla) — Nixtlaoptional
Lag and lag-transform arguments, date features and reversible target transforms applied per series; recursive prediction by default.
SQLite Window Functions — SQLiteoptional
LAG and frame specifications with n PRECEDING and CURRENT ROW used in the SQL lab.

Optional sections

Deep dive · 10 min

The supervised table. For horizon h, each row is (entity, t) with label $y_t$ and features computed from $y_{t-h}, y_{t-h-1}, \dots$ and from information stamped at or before t-h, plus future-known fields stamped for t. Rows near the start of each entity lack lags and are dropped; they should not be filled with another entity's values.

Expanding and exponential windows. An expanding mean over the shifted series, s.shift(h).expanding().mean(), is the all-history level at the cutoff; exponentially weighted means (ewm) use all past data with decaying weights and need the same shift. Seasonal lags use multiples of the period at or beyond h.

Target encoding across time. An entity-level target mean must be computed from rows before each cutoff (an expanding or time-limited window), not from the whole history; otherwise the label leaks into the feature (Day 9).

Strategies in practice. Recursive keeps one model and all horizons coherent but compounds error and needs future-known or forecast values for every lag input at each step. Direct avoids compounding and can use horizon-specific features, at the price of many models. Multi-output networks or stacked models give one model for all horizons. The multistep study registered in this course found multiple-output strategies best on NN5 daily cash-withdrawal series; that result belongs to those series and models, so confirm with your backtest.

Normalisation over time. Scale each series by a level computed from a trailing window ending at the cutoff, or forecast differences or ratios; fit any scaler inside each backtest origin. Fitting it on the full history is the same leak as in Day 9.

Price, promotion and capacity. Price and promotion can be strong predictors, but the coefficient reflects whatever rule set the price: here fares rise when demand is expected to rise, so the fitted relation can be positive even though true elasticities are negative (Day 5). Capacity censors bookings (Day 28): a model of observed bookings learns the cap, not demand.

Global vs local. Montero-Manso and Hyndman argue that global methods are not more restrictive than local ones; still benchmark against local ETS per series and report by segment, because a few high-value series may deserve their own model.

Hands-on lab: The same safe and leaky features in SQL on the airline database · 20 min

Run the query. It builds three predictors of final bookings for 2024 departures: the value 14 rows back, a 7-row mean that ends at the cutoff and a 7-row mean that ends at t. Then answer: (1) Why is the row-based lag equal to a 14-day lag here, and what would break if a flight had a missing date (compare with the Day 6 gap)? (2) By how much does the leaky window improve MAE, and why is the gain limited when the window is seven rows long? (3) Change the leaky frame to ROWS BETWEEN 2 PRECEDING AND CURRENT ROW and predict the new MAE before you run it. (4) Add a PARTITION BY route instead of flight_no and explain why that would be wrong.

WITH f AS (
  SELECT date, flight_no, bookings_final,
         LAG(bookings_final, 14) OVER w AS lag_14,
         AVG(bookings_final) OVER (PARTITION BY flight_no ORDER BY date
                                   ROWS BETWEEN 20 PRECEDING AND 14 PRECEDING) AS roll7_at_cutoff,
         AVG(bookings_final) OVER (PARTITION BY flight_no ORDER BY date
                                   ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS roll7_leaky,
         julianday(date) - julianday(LAG(date, 14) OVER w) AS gap_days
  FROM flight_day
  WINDOW w AS (PARTITION BY flight_no ORDER BY date)
),
ev AS (SELECT * FROM f WHERE date >= '2024-01-01' AND gap_days = 14)
SELECT 'A seasonal naive: bookings 14 rows back' AS feature, COUNT(*) AS departures,
       ROUND(AVG(ABS(bookings_final - lag_14)), 1) AS mae_seats FROM ev
UNION ALL SELECT 'B 7-row mean ending at the cutoff (t-14)', COUNT(*),
       ROUND(AVG(ABS(bookings_final - roll7_at_cutoff)), 1) FROM ev
UNION ALL SELECT 'C 7-row mean ending at t (leaks the target)', COUNT(*),
       ROUND(AVG(ABS(bookings_final - roll7_leaky)), 1) FROM ev
UNION ALL SELECT 'all 2024 rows (compare with departures above)', COUNT(*), NULL
       FROM flight_day WHERE date >= '2024-01-01';

Interview practice · 10 min

Your gradient-boosting demand model has a validation MAE 30% below seasonal naive in a random 5-fold CV. The first production month shows no gain. List the checks you run, in order.

Answer as you would in the interview.

Content version 6 · last verified 2026-10-18 · depends on lightgbm, pandas, numpy, sqlite