promptdojo_

Select, filter, aggregate: the feature query shape — step 1 of 7

ch 36 · sql for ml datasets1/7

SELECT, WHERE, GROUP BY: where training data actually lives

Your training data does not live in a CSV someone hands you. It lives in a database, and somebody has to write the query that turns a million raw rows into a model-ready table. That somebody is increasingly you-plus-Cursor — which means you need to read SQL well enough to catch what the AI got wrong.

Run the editor: that's real SQL executing against sqlite, which ships inside Python's standard library. Same language the big warehouses (Postgres, BigQuery, Snowflake) speak, minus the scale.

The shape of every dataset query

SELECT   user, COUNT(*) AS n, SUM(amount) AS total   -- what columns
FROM     orders                                      -- which table
WHERE    region = 'US'                               -- filter ROWS (before grouping)
GROUP BY user                                        -- one output row per user
HAVING   total > 20                                  -- filter GROUPS (after grouping)
ORDER BY total DESC

The one distinction that separates people who read SQL from people who guess: WHERE filters rows before grouping; HAVING filters the aggregated groups after. "US orders only" is WHERE. "Users whose total exceeds 20" is HAVING. Put a condition in the wrong one and the query still runs — with different, wrong numbers.

Aggregates are your features, again

COUNT, SUM, AVG, MIN, MAX per entity — this is the exact groupby move from the pandas chapter, pushed down into the database where the data already is. For big tables that's the right place: the warehouse aggregates millions of rows and ships you thousands, instead of shipping you millions to aggregate in pandas.

One NULL rule worth knowing now: aggregate functions skip NULLs, and COUNT(*) counts rows while COUNT(col) counts non-NULL values of that column. Two counts that disagree are your fastest missing- data detector — next lessons use exactly that.

Where AI specifically gets this wrong

  • WHERE/HAVING swaps. Runs fine, silently wrong populations. Read every generated query and say out loud which filter applies before grouping and which after.
  • SELECT * into a model pipeline. Grabs every column, including ones you must not train on (like the label's future, or personal data). Name your columns.
  • Aggregating without GROUP BY sanity. Every non-aggregated column in the SELECT must appear in GROUP BY.
  • Assuming the engine will complain. Some engines error on that rule; some (like sqlite) permissively pick a value from an arbitrary row — and permissive is worse, because it runs.
open the editor to run it.