Writing SQL with ChatGPT or Claude, and How to Check the Result

The errors AI-written SQL makes

A query that runs is not a query that is right. The common problems in AI-written SQL are rarely syntax errors, which the database reports at once. They are logic errors, which return a plausible number with no warning.

Grain and fan-out joins

The grain of a table or a query is what one row represents: one order, one shipment, one customer, one day. Most wrong totals in SQL come from joining tables at different grains.

A fan-out join joins a table with one row per order to a table with several rows per order, such as shipments. Every order with two shipments now appears twice, and any sum of order value counts it twice. The query runs, the result looks reasonable, and it is wrong.

The fix is to aggregate the more detailed table to the grain you want before joining. One row per order in, one row per order out.

How to ask for a query

1. Give the table structures. Paste the column names and types of every table involved, and say what one row of each represents.
2. State the grain of the result. “One row per order” or “one row per customer per month”.
3. Define every metric in words. “Active subscriber: billed in the last 35 days and not paused.”
4. Name the filters. Test records, refunds, cancelled orders, time zone and the exact date range.
5. Say which database. PostgreSQL, SQL Server, BigQuery and Snowflake differ in date functions and syntax.
6. Ask for the checks as well as the query. A row-count check after each join, and a reconciliation to a known total.

Worked example: Tessaly

The undefined metric. The board pack said 138,000 active subscribers; finance said 131,000. Neither was right. Since a system migration, the board figure counted 18,500 paused subscribers as active; finance counted anyone billed in the last 60 days. Defined as “billed in the last 35 days and not paused”, the number was 119,500, against 123,200 a year earlier: down 3.0%, not up 12%.

The fan-out join. Average order value rose 8% when shipment data was added to the query. Joining orders to shipments at shipment grain counted every order sent in two parcels twice: 297,000 orders in the quarter became 320,760 rows. The reported average order value was $34.56; the correct figure was $32.00. The fix aggregates shipments to one row per order before the join:

-- Grain: one row per order, Q2 2026
WITH shipments_per_order AS (
  SELECT order_id,
         COUNT(*) AS parcels
  FROM   shipments
  GROUP  BY order_id              -- one row per order before joining
)
SELECT COUNT(*)                         AS orders,           -- expect 297,000
       SUM(o.order_value)               AS revenue,
       SUM(o.order_value) / COUNT(*)    AS avg_order_value,  -- $32.00
       SUM(CASE WHEN s.parcels > 1 THEN 1 ELSE 0 END) AS two_parcel_orders
FROM   orders o
LEFT   JOIN shipments_per_order s ON s.order_id = o.order_id
WHERE  o.order_date >= DATE '2026-04-01'
  AND  o.order_date <  DATE '2026-07-01'
  AND  o.is_test = FALSE
  AND  o.status <> 'refunded';

320,760 rows against 297,000 orders means 23,760 duplicated rows, 8% of orders. Two pricing decisions in the quarter had used the inflated figure.

How to check the result

Count rows after every join

If the result should have one row per order, the row count after each join must equal the number of orders. If it is higher, a join has fanned out. Tessaly’s old query would have failed this check at once: 320,760 against 297,000.

Reconcile to a trusted total

Compare the query’s revenue for the period with the billing ledger or the finance report for the same period, with the same filters. A gap is a question to answer before anyone uses the number.

Read the filters aloud

Check the date boundaries (greater than or equal to the first day, less than the first day of the next period), test records, refunds and time zone. These are where small, persistent errors hide.

Test on a case you can count by hand

Run the query for one customer or one day and check the answer against the raw records.

Try it: a shortened Prompt 04

This is a shortened version of Prompt 04 from the Data & Reporting pack, set up to run on its own. Paste it into ChatGPT, Claude or Gemini and fill in the brackets.

You are a former analytics lead who has rebuilt reporting after a board saw the wrong number. A query that runs is not a query that is right, so you state the grain before writing SQL, check every join for fan-out, and reconcile the result to a known total before anyone uses it.

THE QUESTION:
- What the number is for: [the decision it supports]
- The metric and its exact definition: [e.g. average order value = revenue / orders, at one row per order]
- Period, filters and time zone: [dates, test records, refunds, cancellations]
- Database: [PostgreSQL / SQL Server / BigQuery / Snowflake / other]

THE DATA:
- Each table: [name, columns and types, and what one row represents]
- A trusted total to reconcile to: [e.g. billing ledger revenue for the period]
- An existing query to review, if any: [paste]

Produce:

1. THE GRAIN. What one row of the result represents, and the grain of every table used.

2. THE QUERY. Commented SQL that aggregates any finer-grained table before joining, with every filter stated.

3. THE CHECKS. A row-count check after each join and a reconciliation query against the trusted total.

4. THE REVIEW. If I pasted an existing query: every fan-out, missing filter or boundary error, and its effect on the result.

Use only the column names I give you. If a column you need is missing, say so instead of inventing one.

The full Prompt 04 has eight parts rather than four, and it reads the Analysis Brief built by prompts 01 to 03, so the query uses the metric definitions and data-quality rules already agreed.

Checks before anyone uses the number

• The grain of the result is stated in a comment at the top.
• The row count after every join equals the count of the grain.
• The total reconciles to the ledger or finance figure.
• Every metric is defined in words and used the same way everywhere.
• Date boundaries, test records and refunds are handled explicitly.

Questions

Written by a former Gartner Managing Partner and investment banking SVP

The guides and the prompt templates on this site come from a career spent building these documents: board decks, forecasts, business cases and hiring decisions, as Managing Partner at Gartner, SVP in investment banking and Country Manager at international subsidiaries. The worked examples are published in full on each product page. Browse the Prompt Library.