Write SQL Queries from a Plain-English Question and Your Table Schema

Paste your schema and ask a question in plain English. You get a working SQL query, a line-by-line explanation, and a row-count check query to verify the results.

You getMain query, line-by-line explanation and sanity-check query

Prompt

Prompt
You are a senior data analyst who writes clear, correct SQL for business reporting.

I will paste my table schema (table names, columns, data types, keys) right after this prompt, then ask a question in plain English. If I have not named my SQL dialect (PostgreSQL, MySQL, SQL Server, BigQuery, SQLite), ask first. If my question is ambiguous, for example which date column defines a period or whether to include cancelled records, ask up to 3 short numbered questions in one message and wait for my answers.

Rules:
1. Use only tables and columns in my schema. If something you need is missing, say so instead of guessing a name.
2. Use explicit JOIN ... ON syntax and table aliases. Do not use SELECT *.
3. Flag every one-to-many join that could duplicate rows and explain how the query handles it.
4. Handle NULLs and date ranges explicitly. For timestamps, use >= start and < end, not BETWEEN.
5. Write a separate sanity-check query that counts rows at each step (base table, after filters, after each join).

Output format:
1. Assumptions (bullets)
2. Main query (one code block)
3. Line-by-line explanation (numbered, one line per clause)
4. Sanity-check query (code block) and the result pattern to expect
5. Two ways to extend the query

End with one line on what in my data could still make the result wrong.

Example output

At a glanceSanity-check steps for the revenue queryRow counts to verify the Q3 2024 revenue by region
  1. Step 1: orders in Q3Count orders dated 1 Jul to 30 Sep 2024
  2. Step 2: excluding cancelledShould be lower than step 1
  3. Step 3: after customer joinShould equal step 2, else customers are missing
  4. Main query order_countSum across regions should equal step 3

Example for: PostgreSQL. Tables are orders, customers and order_items. The question was revenue by customer region for Q3 2024, excluding cancelled orders.

1. Assumptions
  • Revenue = quantity × unit_price from order_items. No discounts or tax.
  • Q3 is based on orders.order_date, from 1 Jul to 30 Sep 2024.
  • Orders with a NULL status count as not cancelled.
2. Main query
SELECT c.region,
       COUNT(DISTINCT o.order_id) AS order_count,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE COALESCE(o.status, '') <> 'cancelled'
  AND o.order_date >= DATE '2024-07-01'
  AND o.order_date < DATE '2024-10-01'
GROUP BY c.region
ORDER BY revenue DESC;
3. Line-by-line explanation
  1. SELECT c.region: one output row per region.
  2. COUNT(DISTINCT o.order_id): DISTINCT is needed because the item join repeats each order once per line item.
  3. SUM(oi.quantity * oi.unit_price): adds up the value of every line item.
  4. JOIN customers: attaches a region to each order. Orders without a matching customer are dropped.
  5. JOIN order_items: one-to-many join. Rows multiply here, which is expected when summing items.
  6. COALESCE(o.status, ''): keeps NULL-status orders. Without it, <> would silently exclude them.
  7. Date filters: the half-open range includes all of 30 Sep without catching 1 Oct.
  8. GROUP BY / ORDER BY: totals per region, largest first.
4. Sanity-check query
SELECT 'step 1: orders in Q3' AS step, COUNT(*) AS row_count
FROM orders o
WHERE o.order_date >= DATE '2024-07-01' AND o.order_date < DATE '2024-10-01'
UNION ALL
SELECT 'step 2: excluding cancelled', COUNT(*)
FROM orders o
WHERE o.order_date >= DATE '2024-07-01' AND o.order_date < DATE '2024-10-01'
  AND COALESCE(o.status, '') <> 'cancelled'
UNION ALL
SELECT 'step 3: after customer join', COUNT(*)
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= DATE '2024-07-01' AND o.order_date < DATE '2024-10-01'
  AND COALESCE(o.status, '') <> 'cancelled';

What to expect:

  • Step 2 should be lower than step 1.
  • Step 3 should equal step 2. If it is lower, some orders point to missing customers.
  • The sum of order_count in the main query should also equal step 3.
5. Ways to extend it
  • Add DATE_TRUNC('month', o.order_date) to the SELECT and GROUP BY to get a monthly breakdown per region.
  • Add SUM(...) / COUNT(DISTINCT o.order_id) to get average order value per region.

Risk: if refunds are stored in a separate table, this revenue figure is gross and will overstate net sales.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Limiting the model to columns in your schema stops it from inventing table or column names that break the query.
  • Requiring flags on one-to-many joins catches the most common cause of inflated totals.
  • The step-by-step row-count query lets you verify the logic before you trust or share the numbers.

Use this when you need a report query but are unsure about the joins or filters. Have your table and column names ready, ideally the CREATE TABLE statements. Works in ChatGPT, Claude or Gemini.

Common edits

  • Ask for the same query in a second dialect, such as converting PostgreSQL to SQL Server.
  • Request a version that writes results to a view or temporary table for reuse in Excel or Power BI.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets