SQL for Reproducible Training Data
Every churn model starts with a query that pulls subscribers and their activity from Tunebox's database. Run a careless query twice, a week apart, and you get two different training sets — and no way to tell whether the model improved or the data just changed. The query is part of the model, so it must be as repeatable as the code.
The rule
Freeze the clock: fixed cutoff, features before it, label after it.
Pick one cutoff date. Everything the model may know comes from before it; the thing it must predict comes from after it.
SELECT
s.user_id,
COUNT(e.event_id) FILTER (WHERE e.action = 'skip') AS skips_before_cutoff,
DATE '2026-06-01' - MAX(e.event_date) AS days_since_last_login,
(s.cancelled_at >= DATE '2026-06-01'
AND s.cancelled_at < DATE '2026-07-01') AS churned
FROM subscriptions s
JOIN events e ON e.user_id = s.user_id
WHERE s.signup_date < DATE '2026-06-01'
AND (s.cancelled_at IS NULL OR s.cancelled_at >= DATE '2026-06-01')
AND e.event_date < DATE '2026-06-01'
GROUP BY s.user_id, s.cancelled_at;Three rules are at work:
- A fixed date, not
NOW().NOW()changes every second, so the same query returns different rows tomorrow. Keep the cutoff in a config file likeparams.yaml, versioned with the code. - Features only from before the cutoff.
e.event_date < DATE '2026-06-01'keeps future events out. - The label only from after it.
churnedlooks at the 30 days after the cutoff — exactly the question the model will answer in real life.
Breaking the second rule is called data leakage: the model secretly sees the future during training, scores brilliantly, and then fails in production where the future is not available.
The shortcut
Stand at the cutoff date and ask of every column: "Could I have known this on that day?" If not, it is leakage — or it is the label.
skips_before_cutoff
Counts skips with
event_datebefore 1 June. Knowable on the day. Keep.
Drills
Answer fast — trust the rule.
Why is WHERE signup_date < NOW() bad in a training query?
A feature counts support tickets a user opened in the week after the cutoff. What is wrong?
Where should the cutoff date 2026-06-01 live?
A model scores almost perfect recall on its held-out test set and poor recall live. Which cause does this pattern point to first?
Remember
- Freeze the clock: fixed cutoff, features before it, label after it.
- For each column ask "could I have known this on the cutoff day?"
- Trap:
NOW()and unfiltered event counts; both quietly break reproducibility or leak the future.