Joins and label windows: where leakage is born
A supervised dataset is two things glued together: features (what you knew at prediction time) and a label (what happened after). In SQL, the glue is a join — and the timestamps around that join are where most real-world ML datasets go wrong.
Joins in one breath
SELECT u.user_id, u.plan, f.total_actions, l.churned
FROM users u
LEFT JOIN features f ON f.user_id = u.user_id
LEFT JOIN labels l ON l.user_id = u.user_id
Same semantics as the pandas merge lesson: INNER keeps only
matches, LEFT keeps your whole population with NULLs where the
other side is empty. Same row-explosion trap too: a non-unique join
key multiplies rows. COUNT(*) before and after; if the count grew
unexpectedly, the key wasn't unique.
The label window
Run the editor. Both queries "compute total actions per user." The first sums all months — including the very month the churn label is measured on: Riley's 8 in-window actions inflate the leaky feature from 3 to 11. (Maya's sum happens not to move because her March count is 0 — which is exactly the sneaky part: a feature like "actions in the label month" would hand the model her churn label outright.) The second query cuts features at the label window's start. Different numbers; only one is legal.
The discipline, stated once, used forever:
Every feature must be computable strictly before the moment the label starts. In SQL that means an explicit time predicate —
WHERE event_time < :cutoff— in every feature query, not in your memory of the data.
Point-in-time joins
The harder version: each row has its own cutoff (predict at each
user's signup + 30 days). Then the join condition itself carries the
time rule: ON e.user_id = u.user_id AND e.event_time < u.prediction_time. Window functions like ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC) help you take "the
latest value as of the cutoff" instead of "the latest value ever" —
the second one is leakage with a friendly face.
Where AI specifically gets this wrong
- No time predicate at all. Ask Cursor for "features per user" and it will happily aggregate all of history. The cutoff has to be in your prompt and in the SQL you review.
- "Latest value" without an as-of. The current plan of a user who upgraded after churning is post-label information.
- Inner joins to the label table. Silently drops users with no label row — often the exact negatives your model needed.