Review and optimize a SQL query

Paste a slow or suspicious SQL query and get correctness checks, performance fixes, and a cleaner rewrite you can verify.

Change note: Initial version

Prompt

Review this {{dialect}} query for correctness first, then performance and readability.

Query:
{{query}}

Context: {{context}}
What it is supposed to return: {{intent}}

Please do the following:
1. Correctness review: find logical bugs, such as join fan-out, wrong join type, filters on the outer table of a LEFT JOIN placed in WHERE, NULL traps in comparisons and NOT IN, double counting, time zone or date boundary mistakes, and aggregations at the wrong grain. For each issue, show the offending line and a concrete failure example.
2. Performance review: list the likely bottlenecks in order of impact (unindexed filters, functions on indexed columns, unnecessary DISTINCT, correlated subqueries, large sorts, selecting unneeded columns). Explain what you cannot judge without the execution plan or table statistics, and tell me what to look for in the plan.
3. Rewrite: a corrected, optimized, readable version using clear CTE names and consistent formatting. Keep the output columns identical unless a change is needed; flag any change.
4. Index suggestions: only the ones that clearly help, with the reason and the write-cost tradeoff.
5. Verification: how to prove the rewrite returns the same result as the original (row counts, checksum or EXCEPT comparison on a sample).

If you are unsure whether something is a bug without knowing the data, say so rather than asserting.

Variables

• {{dialect}}SQL dialectselect

  Options: PostgreSQL, MySQL, SQL Server, BigQuery, Snowflake, SQLite

  Default: PostgreSQL

• {{query}}Your querylong

  Default: SELECT c.country, COUNT(*) AS orders, SUM(o.total) AS revenue FROM customers c LEFT JOIN orders o ON o.customer_id = c.id LEFT JOIN order_items i ON i.order_id = o.id WHERE o.created_at >= '2024-01-01' GROUP BY c.country ORDER BY revenue DESC;

  Paste the full query.

• {{context}}Contexttext

  Default: Orders has 20M rows, order_items 80M rows. Query takes about 4 minutes.

  Table sizes, existing indexes, how long it takes.

• {{intent}}Intended resulttext

  Default: Number of orders and total revenue per country since the start of 2024

Example output

**1. Correctness**
- Join fan-out: joining order_items multiplies each order by its number of items, so COUNT(*) counts items, not orders, and SUM(o.total) is repeated for each item. Example: an order of $100 with 3 items adds $300. This is the most serious bug.
- Filter on a LEFT JOIN: WHERE o.created_at >= '2024-01-01' removes customers with no orders, turning the LEFT JOIN into an inner join. That may be what you want; if so, use INNER JOIN to say so.

**2. Performance**
- Biggest cost: joining 80M rows of order_items that the query never uses. Remove the join entirely.
- Check the plan for a sequential scan of orders; a filter on created_at needs an index.
- Cannot judge without the plan: whether the sort is spilling to disk.

**3. Rewrite**
SELECT c.country,
       COUNT(*)  AS orders,
       SUM(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= DATE '2024-01-01'
GROUP BY c.country
ORDER BY revenue DESC;

**4. Index**: CREATE INDEX ON orders (created_at) INCLUDE (customer_id, total); helps this query; adds write cost on every insert.

**5. Verify**: Compare SUM(revenue) with SELECT SUM(total) FROM orders WHERE created_at >= '2024-01-01'. The original will be inflated; the rewrite should match exactly.

Notes

Concrete failure examples for each suspected bug make the review checkable. Paste the EXPLAIN output as well for much more precise performance advice.