PromptScan: pass
Write SQL From a Schema
Turns a plain-English data question into a correct, dialect-specific SQL query using only the tables and columns in the supplied schema, with join logic and assumptions made explicit.
Add a trust badge to your README
[](https://waxmark.app/items/write-sql-from-schema/scan)
Write a {{DIALECT}} query that answers the question below using only the schema provided. If the schema cannot answer the question, say so precisely instead of guessing at a column that is not there.
<schema>
{{SCHEMA}}
</schema>
<sample_rows>
{{SAMPLE_ROWS}}
</sample_rows>
<question>
{{QUESTION}}
</question>
<performance_notes>
{{PERFORMANCE_NOTES}}
</performance_notes>
<sample_rows> and <performance_notes> may be empty. Sample rows are for inferring value formats and cardinality, not for hardcoding. Performance notes may name table sizes, existing indexes, or a latency budget.
Steps:
1. Identify the grain of the answer: one row per what? Write it down before anything else. Most wrong queries have the right columns at the wrong grain.
2. Map every noun in <question> to a table and column in <schema>. Where a noun maps to nothing, stop and report it under "Cannot answer" rather than inventing a column. Where it maps to several candidates, pick one and record the choice under "Assumptions".
3. Decide each join's type from the question's semantics: "customers who have never ordered" demands LEFT JOIN plus IS NULL; "total revenue per customer" with INNER JOIN silently drops customers with no orders. State why each join is inner or outer.
4. Handle the usual traps explicitly: duplicates introduced by one-to-many joins before aggregation, NULLs in comparisons and in COUNT vs COUNT(column), time zones and half-open date ranges (`>= start AND < end`), integer division, and dialect-specific behavior of string comparison.
5. Use {{DIALECT}} syntax only. Prefer CTEs over nested subqueries when there is more than one level of nesting. Alias every table; qualify every column.
6. Re-read the query against <question> one clause at a time and confirm each clause earns its place. Remove anything that does not.
## Output format
**Grain** - one sentence: "One row per ___, filtered to ___."
**Query** - a single fenced `sql` block, formatted with one clause per line, keywords uppercase, and a short `--` comment on any non-obvious join or filter. Must be executable as-is against <schema>.
**Assumptions** - bullet list of every interpretive choice: ambiguous column mappings, inferred date semantics, how ties or NULLs are treated. Each bullet names the alternative you rejected.
**Cannot answer** - only if step 2 found a gap: what the question needs that the schema lacks, and what column or table would close the gap. Omit the section otherwise.
**Verification query** - one short query that checks the main query's row count or a known total, so the result can be sanity-checked against something independent.
**Performance** - 1-3 bullets: which indexes the query would use, the likely expensive step, and one alternative if <performance_notes> indicates large tables. Skip if notes are empty and tables are obviously small.