How to Prompt AI to Write a SQL Query

To prompt AI to write a SQL query that is actually correct, give it three things before you describe what you want: the exact schema of every table involved, the dialect you are running, and a plain-English statement of what one row of the result should mean. Miss any of the three and you get SQL that runs, returns a number, and is quietly wrong. That last failure mode is the whole problem, because a query that errors costs you a minute and a query that silently double-counts costs you a decision.

Here is the template, then the three specific ways model-written SQL goes wrong, and how to catch each one in under a minute.

The prompt template that works

Dialect: PostgreSQL 16

Schema:
  orders(id bigint pk, customer_id bigint fk->customers.id,
         placed_at timestamptz, status text, total_cents int)
  order_items(id bigint pk, order_id bigint fk->orders.id,
              sku text, qty int, unit_cents int)
  customers(id bigint pk, country char(2), created_at timestamptz)

Question: monthly revenue from completed orders, for customers
in Germany, for the last 12 complete months.

One row of the result = one calendar month.
Revenue = sum of orders.total_cents, not recomputed from items.
Timezone for month boundaries: Europe/Berlin.
Return the query only, with a one-line comment above any join
explaining why it cannot duplicate rows.

Four things are doing the work there, and each maps to a real failure.

The dialect line. date_trunc, DATE_TRUNC, EXTRACT, strftime, and DATEADD are not interchangeable. Naming the dialect and version removes an entire class of near-miss output.

The schema block, with keys. Primary and foreign keys matter more than column types, because they are what tells the model which joins can fan out. Paste the real thing rather than describing it. If you have a lot of tables, paste only the ones in scope; more is not better here, and irrelevant tables invite the model to join through them.

The one-row sentence. "One row of the result equals one calendar month" is the single highest-leverage line in the prompt. It is the grain of your result set, and stating it explicitly is what prevents the most common category of wrong answer.

The ambiguity resolution. Revenue could come from the order total or from summing the items. Those two are usually equal and occasionally not, and the difference is refunds, discounts, or a bug. Pick one and say which.

Failure one: the join that quietly multiplies rows

This is the expensive one. Ask for revenue by customer, let the model join orders to order_items because items sounded relevant, and every order with three line items contributes its total three times. The query runs. The number is plausible. It is 2.4x too big.

The check takes fifteen seconds. Before you trust an aggregate, run the same query with the aggregate replaced by count(*) and compare it to the row count you expect:

-- expected: one row per order in scope
select count(*) from orders o
join order_items i on i.order_id = o.id
where o.status = 'completed';

If that count exceeds the number of orders, your aggregate is inflated. When you do need item-level data alongside order totals, the fix is aggregating items in a subquery first and joining the result, and it is worth saying so in the prompt: "aggregate order_items in a CTE before joining, so the join cannot duplicate order rows."

Failure two: NULL semantics in aggregates and filters

Models write where status != 'cancelled' constantly. In SQL, that comparison excludes rows where status is NULL, because NULL is not equal to anything and is not unequal to anything either. Every row with an unset status silently disappears from your result.

The same asymmetry runs through aggregates. count(*) counts rows; count(column) skips NULLs; avg() skips NULLs rather than treating them as zero, which changes the denominator. The PostgreSQL manual's aggregate function reference is explicit about which functions ignore nulls, and it is worth a read if you are reviewing generated SQL regularly.

Two habits fix most of it. Add "treat NULLs explicitly, never rely on default comparison behaviour" to your prompt. Then, on any filter that excludes a value, ask yourself whether the column is nullable, and if it is, write where status is distinct from 'cancelled' instead.

Failure three: date boundaries and timezones

Ask for "last month" and you will get one of five different interpretations: trailing 30 days, trailing 31, the previous calendar month, month-to-date compared to the same period last month, or something involving the current date that shifts every time you run it.

Then, separately, the boundary itself. timestamptz values stored in UTC, bucketed by date_trunc('month', placed_at) without a timezone conversion, put orders placed on the first of the month at 00:30 Berlin time into the previous month. For a business in Central Europe, that is two hours of every month landing in the wrong bucket, forever.

State both in the prompt. Say "last 12 complete calendar months, excluding the current partial month" rather than "last year", and name the timezone the boundaries should use. The SELECT documentation is the reference for how the clauses evaluate if you need to reason about ordering of filters and grouping.

Review the query, not the answer

The habit that separates people who get value from generated SQL from people who get burned is refusing to look at the result before reading the query. The result is always plausible. That is what makes it dangerous.

A three-pass read takes about ninety seconds:

1. Grain. Does one row of output mean what you asked for? Check the GROUP BY against your one-row sentence.

2. Joins. For each join, could the right-hand side have more than one matching row? If yes, is that intentional?

3. Filters. Which rows are excluded, and does any excluded set include NULLs you did not think about?

If it passes all three, run it against a small known slice where you can verify the number by hand. A single customer, a single week. Getting the small case right is much stronger evidence than the full query looking sensible.

When to stop prompting and start writing

Prompting wins on queries you could write but do not want to: multi-table joins, window functions you always have to look up, pivots. It loses on queries where the hard part is knowing your data, because the model cannot see that status has seven values and three of them are legacy typos from a migration in 2023.

That knowledge lives with you, which means the leverage is in the context you supply rather than in prompt phrasing. Our guide to giving AI context about your business covers how to package that once and reuse it, and the broader technique set lives in our prompt engineering guide.

FAQ

Should I paste my whole schema into the prompt?

No. Paste the tables involved plus their keys. Irrelevant tables increase the chance the model joins through something it should not have touched.

Is it safe to run AI-generated SQL against production?

Read-only, on a replica, with a statement timeout. Never let a generated statement be your first contact with a production write path. The same reasoning applies to generated code generally, which we cover in reviewing AI generated code before you ship it.

Why does the AI keep writing queries for the wrong dialect?

Because you did not name one, and the training data skews toward whatever is most common online. Put the dialect and version on the first line of the prompt.

How do I get it to explain its query?

Ask for a comment above each join stating why the join cannot duplicate rows. That specific request produces far more useful commentary than "explain your query", because it forces the model to reason about the failure that matters.

What if the query is right but slow?

That is a separate prompt. Give it the query, the EXPLAIN ANALYZE output, and the row counts of the tables involved. Performance advice without a plan is guesswork, and the model will guess.