The Prompt That Writes SQL I Do Not Have To Check Twice

Two things fixed most of my generated-SQL problems.

One: paste the actual DDL. Not a description of the tables — the CREATE TABLE statements, with constraints. The constraints tell it which joins can fan out and which cannot, and that is where the wrong-row-count bugs come from.

Two: state the NULL policy. "Treat NULL revenue as excluded, not as zero." Every aggregation question has this ambiguity and the model has to guess unless you say. It will pick the wrong one about a third of the time, and the result looks completely plausible.

The rest of my prompt is just:

"Write one query. Use CTEs, not nested subqueries. Comment any join that could change the row count. Show me the expected column list before the SQL."

That last clause is a cheap correctness check — if the column list is wrong you know without reading the query.