Prompt: Turn a Business Question into an SQL Query
Translates a plain-English business question into a runnable SQL query against your schema, plus the one assumption made to resolve any ambiguity.
The prompt
You are a data analyst who turns stakeholder questions into SQL, flagging ambiguity instead of silently guessing at it.
Objective: Write a {{DIALECT — e.g. PostgreSQL, BigQuery, Snowflake}} query that answers: {{BUSINESS QUESTION — e.g. which product categories had declining sales quarter over quarter this year?}}
Context: {{TABLE SCHEMAS — paste the relevant tables, columns and types}}.
Constraints:
- Use only the tables and columns given in the schema above; never invent a table or column name that wasn't supplied
- If the business question is ambiguous (e.g. "declining" could mean several different comparisons), resolve it with exactly one stated assumption, not several competing interpretations
- Use explicit column names, never SELECT *, and add a one-line comment above any non-obvious join, window function or subquery
- Handle NULLs explicitly wherever the result could otherwise silently exclude or miscount rows
Example: "top customers" is ambiguous between "most orders" and "most revenue"; state which one you're answering and why.
Output format: Return the query in a single fenced SQL code block, followed by exactly one sentence stating the assumption made (or "No ambiguity to resolve" if none was needed).
Before answering, check every referenced column exists in the given schema and that only one assumption has been stated, not a list of possibilities.
Example
Input
DIALECT — e.g. PostgreSQL, BigQuery, Snowflake: PostgreSQL BUSINESS QUESTION — e.g. which product categories had declining sales quarter over quarter this year?: which product categories had declining sales quarter over quarter this year? TABLE SCHEMAS — paste the relevant tables, columns and types: orders(id int, product_category text, order_date date, revenue numeric)
Output
```sql
-- Total revenue per category per quarter for the current calendar year
WITH quarterly AS (
SELECT
product_category,
date_trunc('quarter', order_date) AS quarter,
-- COALESCE so a group whose revenue values are all NULL counts as 0, not NULL
COALESCE(SUM(revenue), 0) AS total_revenue Show more
FROM orders
WHERE date_trunc('year', order_date) = date_trunc('year', CURRENT_DATE)
GROUP BY product_category, date_trunc('quarter', order_date)
),
-- Previous quarter's revenue for the same category; a quarter with no orders has no row,
-- so LAG compares against the most recent earlier quarter that does have orders
compared AS (
SELECT
product_category,
quarter,
total_revenue,
LAG(total_revenue) OVER (PARTITION BY product_category ORDER BY quarter) AS previous_quarter_revenue
FROM quarterly
)
SELECT product_category, quarter, total_revenue, previous_quarter_revenue
FROM compared
-- Each category's first quarter has no previous value (NULL), so exclude it explicitly
WHERE previous_quarter_revenue IS NOT NULL
AND total_revenue < previous_quarter_revenue
ORDER BY product_category, quarter;
```
Assumption: "declining" means a category's total revenue in a quarter was lower than in its preceding quarter within the current calendar year, so the query lists every such category and quarter rather than comparing against the same quarter last year.
Why it works
- Restricting the query to the supplied schema stops the model inventing a plausible-looking table or column name that doesn't actually exist.
- Requiring exactly one stated assumption, not a list, forces a decision on ambiguous wording rather than leaving it for the reader to untangle.
- The pre-answer schema check catches an invented column before a broken query reaches a live database.
Why the basic version fails
“Write a query for declining sales by category” without the schema, and without resolving what “declining” means, produces SQL that either references columns that don’t exist or silently picks one interpretation of an ambiguous question without telling you which one it chose.
What this prompt fixes
Supplying the schema removes the column-guessing entirely, and requiring exactly one stated assumption means an ambiguous word like “declining” gets resolved visibly, not silently — so you can check whether the model picked the comparison you actually meant. The NULL-handling requirement also prevents rows being quietly dropped from an aggregate when a join has no match.
Tips per model
- ChatGPT writes clean SQL once given a schema; without one, it will confidently invent table or column names that sound plausible but aren’t there.
- Claude is more likely to flag an ambiguous business question explicitly rather than picking a silent interpretation, which is useful when the phrasing genuinely could mean two things.
- If your business has a specific definition for a term like “active customer” or “declining”, state it directly in the question — it removes the need for the model to assume one at all.