AI SQL: Strengths, 4 Pitfalls, and Fix Checklist

Leverage AI for Routine SQL to Save Time

AI tools like ChatGPT, Copilot, and Gemini excel at simple aggregations (e.g., total revenue by country over 30 days), repetitive boilerplate (date spines, SCD patterns), and syntax translation (7-day rolling averages via window functions). Provide exact table/column details, filters, and metrics in prompts for near-perfect results on these, cutting writing time dramatically since training data covers them well.

For a prompt like "Write SQL for total revenue by country for orders in last 30 days; orders table: order_id, customer_id, country, amount_usd, created_at," AI outputs clean code:

SELECT country, SUM(amount_usd) AS total_revenue_usd, COUNT(order_id) AS order_count
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY country
ORDER BY total_revenue_usd DESC;

This works because specificity prevents assumptions.

Catch AI's 4 Silent SQL Failure Modes

AI queries often run error-free but produce wrong numbers. Fix by pre-aggregating, explicit frames/NULL checks, and dialect specification.

1. Fanout joins inflate sums/counts: AI joins non-unique keys (e.g., orders to order_items), multiplying rows. Aggregate first via CTE: WITH order_totals AS (SELECT customer_id, SUM(amount_usd) AS total FROM orders GROUP BY customer_id). Catch by running COUNT(*) vs COUNT(DISTINCT key) per join key.
2. Wrong window frames: Defaults to cumulative avg, not rolling. Specify ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for 7-day rolling avg. Test on small dataset; defaults vary by DB (e.g., RANGE UNBOUNDED PRECEDING TO CURRENT ROW).
3. NULLs drop rows silently: WHERE status != 'cancelled' excludes NULLs since NULL != value is NULL (false). Add OR status IS NULL. Check with SELECT COUNT(*) WHERE column IS NULL post-query.
4. Dialect mismatches: PostgreSQL NOW() - INTERVAL '30 days' fails in BigQuery; use TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY). Always prompt with DB name ("BigQuery SQL query") to cut errors.

Prompt Template and Review Process for Reliable Output

Use this template for 80% better results:

I’m using BigQuery/PostgreSQL/etc.. Tables: table: cols (types). Write SQL that exact computation. Important: key not unique in table—careful joins; Handle NULLs in col as zero/excluded; One row per grain.

Flagging non-unique keys and grain ("one row per customer per day") prevents double-counting. For tools, use ChatGPT/Claude for complex, Copilot inline, warehouse natives for dialect.

Pre-run checklist (under 5 min):

• Uniqueness: COUNT(*) vs COUNT(DISTINCT key) per join.
• NULL counts in WHERE cols.
• Explicit window frames, test small data.
• Dialect match.
• Row counts per CTE/step.
• Manual 2-3 row aggregation check.

Treat AI as first draft: shines on routine tasks, but review these spots to trust output on production data.