promptdojo_

Mission: SQL feature query lab — step 1 of 7

ch 36 · sql for ml datasets1/7

Mission: the SQL feature-query lab

A feature table is only as trustworthy as the query that built it — so this mission is a lab: write the smallest query worth trusting, throw one leaky user at it, then tighten whatever leaked.

The job: one raw events table in, and out comes (a) a per-user feature table with a leak-proof time cutoff and (b) a quality report that would catch the bugs this chapter taught.

The stages:

  1. Feature query — per-user aggregates with an explicit month < cutoff predicate. The cutoff is a parameter, not a constant buried in the string (the starter shows the ? placeholder pattern — which is also the SQL-injection-safe way to pass values).
  2. Quality gate — row count, duplicate check, per-column missingness, range sanity. Fail loudly on violation.
  3. Stress case — add a user whose only events are inside the label window. Your feature query must return them with zeroed features (or excluded, if that's your documented choice) — not leak their future.
  4. One improvement — COALESCE for NULL-safe totals, a second feature (latest pre-cutoff month), or a HAVING guard: pick one and note why.

Run the editor to see stages 1 sketched. What you're really building is a visible seam in the data: raw events on one side, cutoff-respecting features plus a quality verdict on the other — and a check that will still be standing at the June rebuild.