SQL-from-schema recipe for Qwen

Goal

Turn a table schema plus a plain-English request into correct, dialect-aware SQL — with a self-check pass, so column names and joins actually line up instead of merely looking right.

Generated SQL fails in a specific and dangerous way: it runs, returns rows, and the rows are wrong. Nothing in your tooling catches that. This recipe is built around forcing the failure to be visible.

Recipe

1. Give it the ground truth

Paste the exact CREATE TABLE statements — or the ORM models, or \d+ output. Not a description of the schema; the schema. Include:

• Column types and nullability. NULL semantics drive half the wrong answers.
• Primary and foreign keys, and unique constraints — they determine join multiplicity, which determines whether your SUM is right.
• Indexes, if performance matters.
• Enum values and their meaning, if a status column drives the filter.

State the dialect: Postgres, MySQL, SQLite, SQL Server, BigQuery, DuckDB.

2. State the request precisely

The three things people leave out, in order of how often they cause a wrong answer:

• Grain. "One row per customer per month" — say it explicitly. Ambiguous grain is the number-one cause of silently wrong aggregate queries.
• NULL handling. Do customers with no orders appear with zero, or not appear? That's the difference between a LEFT JOIN and an INNER JOIN, and the model cannot guess your intent.
• Boundaries. Are date ranges inclusive at both ends? What timezone are the timestamps in, and what timezone should the grouping use? Ties in a top-N — dense rank, or arbitrary?

3. Ask for the query plus a rationale

Write the SQL, then explain in 2–3 lines which tables you joined on which keys and why. Use only columns that appear in the schema above. If the request is ambiguous, state the interpretation you chose rather than picking silently.

That last clause is what surfaces the ambiguity you didn't notice you had.

4. Self-check pass

A separate turn, not a clause in the first one:

Verify every table and column you referenced exists in the schema above. List any that don't. Check that GROUP BY covers every non-aggregated column in SELECT. Check each join for multiplicity: could any join produce more rows than its left side, and if so does that inflate an aggregate? Check whether filters belong in WHERE or HAVING.

Run it as its own turn because verification against the generation in the same breath tends to rubber-stamp.

5. Dry-run on three rows

Here are 3 sample rows per table. Walk through the query and show the exact result set. Confirm the row count and grain match what I asked for.

This catches fan-out, wrong-direction joins, and off-by-one date boundaries faster than reading the SQL does — and much faster than running it on real data and eyeballing plausible-looking numbers.

6. Run it read-only, on a bounded slice

First execution should be against a replica or with a LIMIT, and never inside a transaction that writes. Compare the row count against your expectation from step 5 before you trust any number in the output.

Common traps to prompt against

• Invented columns. created_date when your schema says created_at. The most common single failure, and the self-check pass is what catches it.
• Join fan-out inflating SUM/COUNT. Joining orders to order_items and summing orders.total multiplies the total by the item count. Join to a pre-aggregated subquery, or aggregate before joining.
• COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col). Three different questions; models pick the first one.
• NOT IN with NULLs returns nothing at all. Use NOT EXISTS.
• Filtering an outer join in WHERE silently turns it into an inner join. The condition belongs in the ON clause.
• Dialect drift: LIMIT vs TOP vs FETCH FIRST; || vs CONCAT; date_trunc vs DATE_FORMAT vs strftime; true vs 1; ILIKE vs LOWER(...) LIKE.
• Timezones. date_trunc('day', ts) on a timestamptz groups by UTC days, which is not what a report for a New York team means by "day".

Output

Final SQL in one block, the join rationale, the assumptions it had to make, and the expected grain. Keep the assumptions — they're what you check when the number looks wrong three weeks later.

Where this recipe does not help

A schema you don't understand. If you can't tell whether the returned number is plausible, no prompting technique saves you — you're just automating the production of confident wrong answers. Learn the two or three tables first.