Core day 7 of 60 · Week 2 · Part I · application depth

SQL and Analytical Data Modeling

A pickup report joins flight results to the booking curve and shows 13 million passengers for 2024 on a network that carried about one million. Where does the error come from, how do you fix the query, and which numbers on the same dashboard were wrong without looking wrong?

By the end: You find a join-grain error in a query, fix it by choosing the grain explicitly, state the KPI error it caused as a number, and say which metrics it did and did not distort.

At a glance

Write the grain of every table before you join; a right side that is not unique on the keys multiplies sums silently while averages look fine.

Grain
One row per flight-date, per order, per meter-hour: write it for both sides before any join.
Fan-out
A join returns sum over keys of L_k x R_k rows; 13 snapshots per flight make every sum 13x.
Ratios survive
Averages and ratios of sums are unchanged, which is why the bug hides.
Three fixes
Filter to one snapshot, pre-aggregate, or join at a common coarser grain.
Snapshots
Never sum a snapshot across time; pick the state at decision time.

Recall · 5 min

Without notes: in yesterday's March query, why did removing exact repeats with DISTINCT not resolve every duplicate key in flight_day_raw, and what extra information would you need to pick the right version of a row?

Open due reviews

Concept · 10 min

Every table answers one question: what does one row represent, its Grain (unit of analysis)? flight_day has one row per flight and date; booking_curve has 13, one per snapshot. Join them on (date, flight_no) and each flight-date meets 13 partners. Nothing errors, nothing warns, and every SUM on the left table is 13 times too large: a fan-out, the most common silent bug in analytical SQL.

Joining a 13-snapshot curve to flight results multiplies every sum by 13 and leaves averages untouched

Predict first

After the unfiltered join, what happens to SUM(revenue) / SUM(passengers) (average revenue per passenger)?

Text description of the figure

Two small tables, flight-day results and booking-curve snapshots, with a grain selector, a join toggle and an aggregation selector. Turning on the unfiltered join shows each flight-date row repeated once per snapshot, the row count and SUM rising 13-fold while AVG stays the same; filtering to one snapshot restores the grain.

How one join multiplies every row by 13 Diagram

flight_day1 row per flight-date
- to Right side unique?
booking_curve13 rows per flight-date
Right side unique?
- ignored: to 13 copies per row
no
Filter one snapshotdays_to_departure = 30
- to Grain kept
13 copies per rowSUM x 13, AVG unchanged
- to Report
Grain kept1 row per flight-date
Report13.2 million passengers

The check before the join: SELECT date, flight_no, COUNT(*) FROM booking_curve GROUP BY 1, 2 HAVING COUNT(*) > 1. Any row means a fan-out is coming.

A join, drawn row by row Illustration

One square per flight-date: the grain the KPIs are defined on.

Every column is 13 high. Sums scale by exactly 13; the average divides by 13 times the rows, so it does not move.

April flights now get fewer copies (35% of joined rows instead of 50%), so the average leans toward the emptier March flights.

Seeded tables from the Day 7 grain explorer: 48 flight-dates, 13 booking-curve snapshots each; in the last panel 6 snapshots are removed from half the flight-dates, April first.

Each column is one flight-date and each square one row of the result. A uniform fan-out scales sums and hides in averages; an uneven one moves the averages too.

Today's run: 2024 passengers, millions Chart

Broken join13.0 x in every market13.24 million
Point-in-time join1.02 million

Average load factor is 0.7766, 0.8049 and 0.8157 by market in both versions; only totals and counts are wrong.

Three deliberate fixes Comparison

Criterion	Filter the right side	Pre-aggregate	Coarser common grain
How	AND b.days_to_departure = 30	GROUP BY date, flight_no in a CTE, then join	Aggregate both sides to route x month, then combine
When to use it	One state is the right one: the snapshot at decision time	You need a summary of many rows: total cancellations	The question is asked at that grain
Watch out for	Flights missing that snapshot drop out of an inner join	Summing snapshots double-counts; sum events only	Detail is gone; you cannot drill back down

Event rows and snapshot rows Comparison

Criterion	Event	Snapshot
One row is	Something that happened: bookings made in one interval	A state at a moment: cumulative bookings 30 days out
Summing over time	Meaningful: events add up	Double-counts: each state contains the earlier ones
2024 total from booking_curve	SUM(bookings) = 1,129,194	SUM(cumulative_bookings) = 9,522,688, 8.4 x too many

Also separate event time (when it happened) from processing time (when it was recorded). A late revenue update has an old event time and a new processing time.

Before a join, write the grain of both sides and of the result. A join keeps the left grain only if the right side is unique on the join keys.
Fix a fan-out deliberately: a point-in-time filter, pre-aggregation, or a common coarser grain.
Separate event rows from snapshot tables, event time from processing time, and keep dimension history with type-2 rows. Window functions build per-row features (Day 24).

Assumptions

The join keys are clean and typed consistently (dates as the same ISO text, no casing differences); Day 6 issues break joins before grain does.
The snapshot schedule is complete for every flight-date; if some flights lack early snapshots, the fan-out factor varies by row and the damage stops being a constant multiple.
The decision time is known, so you know which snapshot is the legitimate point-in-time value.

Application and workplace decision · 10 min

A colleague's 2024 pickup report joins flight_day to booking_curve on date and flight number. Run the query: it computes the colleague's version and a fixed version side by side, by market. Then state (a) the KPI error, (b) which metrics on the report were affected, and (c) the fix you would merge.

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

SQL (airline sample database)

-- 2024 passengers by market: fan-out join vs point-in-time join
WITH broken AS (
  SELECT f.market, SUM(f.passengers) AS pax, ROUND(AVG(f.load_factor), 4) AS avg_lf
  FROM flight_day f
  JOIN booking_curve b ON b.date = f.date AND b.flight_no = f.flight_no
  WHERE f.date BETWEEN '2024-01-01' AND '2024-12-31'
  GROUP BY f.market
),
fixed AS (
  SELECT f.market, SUM(f.passengers) AS pax, ROUND(AVG(f.load_factor), 4) AS avg_lf,
         SUM(f.bookings_final) AS bookings_final, SUM(b.cumulative_bookings) AS bookings_30d
  FROM flight_day f
  JOIN booking_curve b
    ON b.date = f.date AND b.flight_no = f.flight_no AND b.days_to_departure = 30
  WHERE f.date BETWEEN '2024-01-01' AND '2024-12-31'
  GROUP BY f.market
)
SELECT x.market, x.pax AS pax_broken, y.pax AS pax_fixed,
       ROUND(1.0 * x.pax / y.pax, 2) AS inflation,
       x.avg_lf AS avg_lf_broken, y.avg_lf AS avg_lf_fixed,
       ROUND(1.0 * y.bookings_30d / y.bookings_final, 3) AS booked_share_30d
FROM broken x
JOIN fixed y ON y.market = x.market
ORDER BY x.market;

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

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

Join cardinality: how many rows a join returns6 minThe row count of any join derived from per-key counts, a 4-order example worked by hand, and why the output can be far larger than either input.

Join cardinality as arithmetic over keys Formula

\begin{gathered}|A \bowtie B| = \sum_k L_k R_k \\ |A \;\text{LEFT JOIN}\; B| = \sum_k L_k \max(R_k, 1) \\ \sum_{\text{joined}} x = \sum_k R_k\, S_k\end{gathered}

$k$: one value of the join key (an order_id, a (date, flight_no) pair, a meter id)
$L_k, R_k$: rows with key k on the left and on the right side
$L_k R_k$: an inner join pairs every left row of k with every right row of k: the product, not the larger count
$\max(R_k, 1)$: a left join keeps an unmatched left row once, with NULLs on the right
$S_k$: the sum of a left-side measure x over the left rows of key k; after the join each copy counts R_k times

The left grain survives only when every R_k is 0 or 1 (inner) or at most 1 (left). Two fan-outs on one key multiply: sum of L_k R_k P_k rows. Every fan-out question is a question about the R_k.

Relationship names are statements about L_k and R_k Map

Many-to-one

The safe lookup: every left row meets at most one partner. Left grain kept.

Many-to-many

L_k x R_k rows per key: the product, not the larger count.

One-to-one

Both sides unique. Left grain kept.

One-to-many

A fan-out: each left row is copied R_k times.

Only the right column decides whether the left grain survives. The proof is cheap: max over k of R_k must be 1, which is what GROUP BY keys HAVING COUNT(*) > 1 on the right side checks (pandas: validate='many_to_one').

Worked by hand: 4 orders joined to their shipments Table

order_id	revenue x	shipments R_k	Inner join rows	Revenue counted
1	120	2	2	240
2	80	1	1	80
3	200	3	3	600
4	60	0 (not shipped)	0 (left join: 1)	0 (left join: 60)
Truth	SUM 460, AVG 115	6 shipments in total	4 orders	460
Inner join	-	-	6	SUM 920, AVG 153.3
Left join	-	-	7	SUM 980, AVG 140.0
Pre-aggregate shipments, then join	-	-	4	SUM 460; shipment count kept as a column

Hypothetical e-commerce tables, verified in SQLite. The fan-out is uneven (R_k = 2, 1, 3, 0), so even the average moves: big orders ship in more parts and get more copies. Pre-aggregating the right side restores one row per order.

Anatomy of a fan-out: what SUM and AVG see3 minWhy sums inflate by exactly 13 and averages do not move, and when they do.

What a fan-out does to SUM and AVG Formula

\begin{gathered}\text{SUM}_{\text{joined}} = \sum_{k} m_k\,x_k \\ \text{AVG}_{\text{joined}} = \frac{\sum_k m_k\,x_k}{\sum_k m_k}\end{gathered}

$x_k$: the left-side value of flight-date k (passengers, load factor)
$m_k$: number of right-side rows that match key k: 13 for a full booking curve, 0 drops the row
$m_k = m$: uniform fan-out: SUM is m times too large, AVG is unchanged
$m_k varies$: AVG becomes a weighted mean; biased whenever m_k is related to x_k
$\frac{\sum_k m\,r_k}{\sum_k m\,p_k}$: a ratio of sums (revenue / passengers) under a uniform fan-out: the m cancels, which is why rate tiles look right

Every fan-out question reduces to one: what are the match counts m_k, and are they related to what you measure?

When a fan-out is worse than a constant multiple Overview

Missing snapshots
Flights with fewer snapshots get fewer copies: averages shift too.
Two fan-outs
Orders x shipments x payments: an order with 3 shipments and 2 payments appears 6 times.
One hot key
Join cost grows with sum of L_k R_k: one key with 10,000 rows per side alone makes 100 million rows.
Dirty keys
Case or type mismatches drop rows silently (Day 6).
Filtered windows
Filter after computing windows, or LAG reads the wrong row.

Window functions, defined and worked by hand7 minThe frame as a set of rows, ROWS vs RANGE written as math, six days of meter data computed by hand, then the frames that leak.

A window value is an aggregate over a frame that moves with the row Formula

\begin{gathered}w_i = \mathrm{agg}_{j \in F(i)} x_j \\ F_{\text{ROWS}}(i) = \{ j : i-a \le j \le i-b \} \\ F_{\text{RANGE}}(i) = \{ j : t_i - a \le t_j \le t_i - b \}\end{gathered}

$i, j$: positions of rows inside one partition, after sorting by the ORDER BY key
$t_i$: the ORDER BY value of row i: a date as a day number, a timestamp, a sequence id
$a, b$: the frame offsets: 'a PRECEDING AND b PRECEDING'; b = 0 means the frame reaches the current row
$F_{\text{ROWS}}$: counts positions: a fixed number of rows, however much time they span
$F_{\text{RANGE}}$: counts in units of the ordering key: a fixed span of time, however many rows fall in it
$\operatorname{agg}$: AVG, SUM, MIN, COUNT ... over the frame; LAG(x, k) is simply x at position i - k

The two definitions agree exactly when there is one row per unit of t (a complete date spine). A missing date shrinks the RANGE frame and stretches the ROWS frame.

Worked by hand: a substation's daily load (MWh) with 4 March missing Table

date	x (MWh)	LAG(x, 1)	AVG, ROWS 2 to 1 PRECEDING	AVG, RANGE 2 to 1 days PRECEDING	running SUM
03-01	40	NULL	NULL	NULL	40
03-02	44	40	40.0	40.0	84
03-03	42	44	(40 + 44) / 2 = 42.0	42.0	126
03-05	50	42 (two days back)	(44 + 42) / 2 = 43.0 - spans 3 days	42.0 - only 03-03 is within 2 days	176
03-06	47	50	(42 + 50) / 2 = 46.0 - reaches back to 03-03	50.0 - 03-04 is missing	223
03-07	45	47	(50 + 47) / 2 = 48.5	48.5	268

Hypothetical energy data, recomputed with SQLite 3.53 (RANGE ordered by julianday(date)). Until the gap both frames agree; after it the ROWS frame quietly reaches further back in time, and the RANGE frame holds fewer rows.

Where window functions run inside one query Sequence

FROM and JOIN
Rows are assembled; a fan-out happens here, before anything else sees the rows.
WHERE
Rows are filtered before any window reads them: a June filter here hides May from LAG.
GROUP BY, HAVING
Groups collapse; a window then runs over the groups, not the raw rows.
Window functions
Per partition after a sort, O(n log n) and usually the dominant cost; sliding frames then update in O(n).
ORDER BY, LIMIT
Applied last. To filter on a window value, or keep full history for features, filter in an outer query.

Logical evaluation order. Features that need history are computed in a CTE over all dates and filtered afterwards.

Anatomy of a window function Stack

AVG(passengers)The value computed for each row, without collapsing rows.
PARTITION BY flight_noRestart the calculation for each flight.
ORDER BY dateDefine what 'previous' means.
ROWS BETWEEN 7 PRECEDING AND 1 PRECEDINGThe frame: the seven rows before this one, excluding it.

Rows are not days. With gaps in the dates (flight_day_raw), a 7-row frame spans more than 7 days; use a date-based condition instead.

Frames on a calendar with a hole Illustration

Rows are not days. After a gap, a row-count frame silently reaches further back; a date-based frame keeps its meaning and holds fewer rows.

With ORDER BY and no frame clause the frame is RANGE up to the current row including its peers: both rows dated 06-02 already see each other.

Seeded synthetic passenger counts for one flight over 21 days with a 4-day hole. Frame membership computed with the SQL frame rules.

The same current row, three frames. Row counts and day counts part ways as soon as a date is missing; a frame that reaches the current row contains the answer.

Pick the frame for the question Table

Question	Frame	Safe when
Last 7 rows	ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING	one row per entity and date (a date spine)
Last 7 days	RANGE over a day number, 7 PRECEDING AND 1 PRECEDING	always; the row count varies
Running total	ROWS UNBOUNDED PRECEDING	always; the default frame groups tied dates
Feature for day t	any frame ending at 1 PRECEDING	never ends at CURRENT ROW

In SQLite and PostgreSQL, RANGE with an offset needs exactly one ORDER BY term: a number such as julianday(date) in SQLite, or a date with an interval in PostgreSQL.

Three window features for ZX101 (lab output) Table

date	passengers	LAG 7 rows	avg previous 7 rows	avg incl. today
2024-06-08	173	140	151.4	156.1
2024-06-09	122	173	156.1	148.9
2024-06-10	132	172	148.9	143.1

The last column contains the value it is meant to predict, so it leaks; the previous-7 window ends one row before the current date.

Choosing and asserting a fix3 minFilter, pre-aggregate or coarsen, picked from the question, then guarded by a check in the pipeline.

Choosing the fix from the question Diagram

Right side unique on the keys?
- no: to Need the state at one moment?
yes
Join as isassert it in the query
Need the state at one moment?
- no: to Need a summary of many rows?
yes
Point-in-time filterone snapshot per key
Need a summary of many rows?
- no: to Coarser common grain
yes
Pre-aggregate eventsGROUP BY the keys, then join
Coarser common grainaggregate both sides first

DISTINCT is not on the map. It removes only rows identical in every selected column and can merge genuinely different rows.

Assert the grain where the join happens Table

Where	Check	Fails when
Before writing SQL	the grain of left, right and expected result, one line each	you cannot say what one result row is
SQL, before the join	GROUP BY keys HAVING COUNT(*) > 1 on the right side	any key has two partners
SQL, after the join	COUNT(*) and COUNT(DISTINCT keys) of the result	the two counts differ
pandas	merge(..., validate='many_to_one')	the right side is not unique on the keys
Reconciliation	one total against an external number	finance or the schedule disagrees

pandas validate options are one_to_one, one_to_many, many_to_one and many_to_many (no check). Put the check in the pipeline, not in a reviewer's memory.

Joins that lose rows2 minInner joins, WHERE filters on outer joins and NULL keys drop rows without an error.

Joins also lose rows silently Comparison

Criterion	INNER JOIN	LEFT JOIN	Anti-join
Left rows without a match	Dropped	Kept, right columns NULL	Only these are returned
Typical silent bug	Customers with no orders vanish from a churn table; flights without a 30-day snapshot too	A WHERE filter on a right column turns it back into an inner join	None: it is the diagnostic
Check	Count left rows before and after	Put right-side filters in ON, not WHERE	LEFT JOIN ... WHERE right key IS NULL lists the orphans

NULL never equals NULL in a join condition, so rows with a NULL key never match. Fan-out adds rows; these lose them. Both pass without an error.

Events, snapshots and two clocks3 minWhat may be summed over time, and event time against processing time.

Events add up; states pile up Illustration

13 rows, each the running total at that moment. The row at 30 days already contains everything booked before it.

Differences of the states are events. Events are additive over time; states are not.

Summing states counts every early booking again at each later snapshot. The factor is not 13 and not constant: it depends on how early each flight sold.

Seeded booking curves from the Day 7 grain explorer (48 flight-dates, snapshots at 90, 60, 45, 30, 21, 14, 10, 7, 5, 3, 2, 1, 0 days). The sample database gives its own factor for 2024 (8.4×).

The same booking curve as 13 states and as 13 events. Summing the states over time counts early bookings again at every later snapshot.

Which measures may be summed, and over what Table

Kind	Examples across sectors	Sum over time?	Sum over entities?
Additive (events)	units sold, kWh consumed, card transactions, bookings made in an interval	yes	yes
Semi-additive (states)	account balance at month end, stock on hand, cumulative bookings at 30 days	no: pick one moment	yes, at one moment
Non-additive (ratios)	conversion rate, gross margin %, load factor	no	no: sum the parts, then divide

Kimball's three kinds of fact. A ratio of sums is right at any grain; an average of ratios weights every flight equally, which is a different question.

Slowly changing dimensionsadvanced3 minKeeping history so old facts join to old attributes.

A type-2 dimension: one row per version (example rows) Table

customer_id	segment	valid_from	valid_to
C-1042	mass retail	2022-01-01	2023-06-30
C-1042	affluent	2023-07-01	9999-12-31

A bank's customer dimension. Join facts on the key and on date between valid_from and valid_to, so a 2022 transaction keeps its 2022 segment. Joining to today's row rewrites history. (Illustrative id and dates.)

Joining a fact to the right version Diagram

Transaction on 2022-05-10customer C-1042; may arrive late, in 2024
Match the versionvalid_from <= event date <= valid_to
match
Version 1: mass retailthe attributes it had then

A late-arriving fact takes the version valid at its event time, not the newest row. Validity windows must not overlap, or this as-of join fans out again.

Idempotent loads and backfillsadvanced2 minRe-running a job must give the same table, not more rows.

Re-runs that cannot duplicate rows Sequence

Name the partition
The date range or key set this run is responsible for.
Replace
DELETE the partition and INSERT it again in one transaction, or upsert on the key.
Unique input
An upsert batch must itself be unique on the key; PostgreSQL rejects touching one row twice.
Assert
Row count and key uniqueness of the partition after the load.
Backfill the same way
Parameterise the range; write to staging; compare totals; then swap.

A retried append is the most common source of the duplicate keys found on Day 6.

Memory anchor

Write the grain of both sides before the join; a right side that is not unique on the keys multiplies everything you sum.

Useful for

Reviewing any analytical query or dbt model before its numbers reach a report - order and shipment tables, claims and claim lines, accounts and transactions.
Building model features from snapshot and event tables (account balances, sensor states, booking curves) without double counting or peeking past the decision time.
Explaining a KPI discrepancy between two reports that join the same tables differently, for example finance revenue against a product dashboard.

Not suitable for

Fixing bad keys - grain checks assume clean keys; duplicate or inconsistent keys (Day 6) must be resolved first.
Replacing a semantic layer - a documented metric definition with an owner prevents the same join being rewritten ten ways.

Traps

DISTINCT as a fan-out fix

Adding DISTINCT hides duplicated rows only when they are identical in every selected column; it also merges genuinely different rows that happen to match, so totals can become too small.

How to avoid it

Fix the grain where it breaks - filter or pre-aggregate the right side to one row per key - and assert uniqueness.

Summing cumulative snapshots

Cumulative counts are states, not events. Summing them across snapshots counted 2024 bookings 8.4 times (9,522,688 vs 1,129,194); summing month-end account balances over a year in a bank does the same thing.

How to avoid it

Sum event (incremental) columns; for state columns pick one snapshot per key or take the last value.

Senior reasoning

A senior writes the grain of every input and of the output before writing the join, checks row counts before and after, and treats a plausible-looking rate as no evidence that the query is right. When a bug ships, they quantify which numbers were wrong and which were not, state the condition under which the unaffected ones were safe, and add an automated uniqueness test so the fix outlives the incident.

Interview question

"Revenue in your dashboard is 13 times what finance reports. Walk me through how you find the cause." How would you structure the answer?

Outline your answer first.

Recall cards scheduled after completion

When does a join keep the left table's grain, and how do you check it before running the report?
After a uniform 13x fan-out, which metrics are wrong and which survive? When do the survivors break too?

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

Prerequisites and related concepts

References

Kimball Dimensional Modeling Techniques: Grain — Kimball Groupcore
Declaring the grain first, choosing the atomic grain, and never mixing grains in one fact table - the discipline behind today's fix.
SQLite Window Functions — SQLiteoptional
Syntax and semantics of LAG and ROWS frames used in the window-function preview and the hands-on lab. (Built-in window functions; frame specifications)
Kimball Dimensional Modeling Techniques: Additive, Semi-Additive, and Non-Additive Facts — Kimball Groupoptional
Additive, semi-additive and non-additive facts - which measures may be summed over time and which must be recomputed from their parts.
Window Function Calls (PostgreSQL documentation, section 4.2.8) — PostgreSQL Global Development Groupoptional
Frame modes ROWS, RANGE and GROUPS, peers, and the default frame with ORDER BY (up to the last peer of the current row).
pandas.DataFrame.merge (pandas API reference) — pandas development teamoptional
The validate argument of merge, which asserts one-to-one, one-to-many or many-to-one keys at the join.
Kimball Dimensional Modeling Techniques: Type 2 (Add New Row) — Kimball Groupoptional
Type-2 dimensions add a row per change with effective and expiration dates, so facts join to the version valid at their date.
Tidy Data — Hadley Wickham, 2014optional
Each type of observational unit in its own table - flight results and booking snapshots are different units and must be combined at an explicit grain.

Optional sections

Deep dive · 10 min

Fan-out in disguise. Fan-outs also come from many-to-many joins on dimensions (a route mapped to two markets after a reorganisation), from joining two fact tables on a shared dimension (bookings and cancellations by date produce every booking x every cancellation that day), and from LEFT JOINs to tables with duplicate keys. The test is always the same: is the right side unique on the join keys?

Slowly changing dimensions and backfills. A type-1 dimension overwrites attributes (history lost); type 2 adds a versioned row with validity dates, and facts join on key and date range. When a definition changes - say, passengers starts to include infants - a backfill recomputes history under the new definition. Make backfills reproducible: parameterise the date range, write to a staging table, compare row counts and totals with the old version, then swap.

Idempotent re-runs. An Idempotent pipeline gives the same result whether it runs once or three times: overwrite a partition (DELETE for the date range, then INSERT) or MERGE on the key, never blind INSERT. A retried job that appends creates exactly the duplicate keys you found on Day 6.

Window functions on gappy data. LAG(x, 7) means seven rows back, not seven days back. On flight_day_raw with its gaps, the same expression silently compares with a different weekday. Join to a complete calendar first, or use a self-join on date(date, '-7 days'). Day 24 builds on this.

Hands-on lab: Point-in-time window features with LAG and rolling averages · 15 min

Run the query, which computes three features for flight ZX101 over its full history and then shows 12 days of June 2024. Then:

Explain why pax_avg_incl_today is not a legitimate feature for predicting that day's passengers, while pax_avg_prev_7d is (Day 2 availability, Day 24 leakage).
Rewrite pax_same_weekday_last_week so it stays correct on flight_day_raw, where some dates are missing.
Why is the window computed in a CTE over all dates before filtering to June? What changes if the WHERE clause moves inside the CTE?

-- Window features over the full history, then display a slice
WITH feats AS (
  SELECT date, flight_no, passengers,
         LAG(passengers, 7) OVER w AS pax_same_weekday_last_week,
         ROUND(AVG(passengers) OVER (w ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING), 1) AS pax_avg_prev_7d,
         ROUND(AVG(passengers) OVER (w ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 1) AS pax_avg_incl_today
  FROM flight_day
  WHERE flight_no = 'ZX101'
  WINDOW w AS (PARTITION BY flight_no ORDER BY date)
)
SELECT * FROM feats
WHERE date BETWEEN '2024-06-08' AND '2024-06-19'
ORDER BY date;

Interview practice · 10 min

You are given orders (one row per order) and shipments (one row per shipment; an order can ship in parts). Write, in words, how you would report revenue per day and shipped units per day in one table.

Answer as you would in the interview.

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

1 Recall · 5 min

2 Concept · 10 min