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.

Generated Outputs (1)

1 weeks ago

ChatGPT

gpt-4-turbo

1 weeks ago

Generated Output

Expected columns: month (date), active_customers (int), net_revenue (numeric)

WITH paid AS (
-- one row per invoice; no fan-out, invoice_id is unique
SELECT customer_id, date_trunc('month', paid_at) AS month, amount
FROM invoices
WHERE paid_at IS NOT NULL AND amount IS NOT NULL
)
SELECT month, count(DISTINCT customer_id) AS active_customers, sum(amount) AS net_revenue
FROM paid GROUP BY month ORDER BY month;

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

Generated Outputs (1)