Check AI-generated SQL by defining the required result, testing a small dataset with known answers, and reconciling totals before using the output. A query that runs successfully can still answer the wrong business question.

Start with a result you can calculate yourself

Imagine three orders: order 1 is completed for $100, order 2 is completed for $50, and order 3 is cancelled for $80. Completed-order revenue should be $150. This is a synthetic learning dataset; it is not company performance data.

WITH orders(order_id, status, amount_usd) AS (
 VALUES (1, 'completed', 100),
        (2, 'completed', 50),
        (3, 'cancelled', 80)
)
SELECT SUM(amount_usd) AS revenue_usd
FROM orders
WHERE status = 'completed';

This query uses a VALUES common table expression supported by PostgreSQL and DuckDB. Its expected answer is 150. If an AI-generated version returns 230, it probably omitted the status rule.

Seven checks before accepting the result

1. Wrong metric. Ask whether revenue means booked, paid, shipped or recognised revenue. Specify treatment of refunds and tax. A column named amount does not establish a business definition.
2. Join multiplication. If order 1 has two support tickets, joining orders to tickets repeats its $100. The two completed orders then appear to total $250. Aggregate tickets to one row per order before joining, or use EXISTS when you only need to test whether a ticket exists.
3. Missing records. An INNER JOIN removes orders with no matching lookup row. Count unmatched keys before choosing an inner join. A LEFT JOIN can preserve them, but a right-table filter in WHERE can discard them again.
4. Date boundaries. For timestamp data, use a half-open interval: greater than or equal to the start and less than the next period’s start. Specify the reporting timezone. An end date at midnight can accidentally exclude most of the final day.
5. Null handling. COUNT(*) counts rows; COUNT(column) counts non-null values. Do not replace every missing amount with zero unless zero has the correct business meaning.
6. Incorrect aggregation. SUM(DISTINCT amount_usd) does not repair duplicate joins: two legitimate $100 orders would become $100 instead of $200. Deduplicate by the business key, using a documented rule.
7. Unsupported explanation. A revenue decrease does not prove a campaign caused it. Separate observations from hypotheses and identify the extra evidence needed to test a cause.

Give the assistant a better specification

Use a prompt such as: “Write read-only PostgreSQL SQL. One row represents one order. Sum completed orders in August using UTC timestamps. Exclude cancelled orders. Return the query, assumptions, and three tests; do not invent table names.” Supply only approved schema information and synthetic examples when working outside your organisation’s approved tools.

Reconcile before sharing

Compare row counts, distinct order counts and revenue before and after each join. Inspect the largest changes. Keep your test dataset and expected result with the query so a later rewrite can be checked against the same evidence.

Practice: Change order 2 to $100. Expected completed revenue becomes $200. This exposes the SUM(DISTINCT amount) mistake immediately.

Continue with the SQL joins guide and the revenue-drop investigation.

Sources and further reading

• PostgreSQL: table expressions
• PostgreSQL: aggregate functions

Found an error? Send a correction.