Writing SQL Queries for Data Analysis with AI Assistance

SQL is the universal language of data work. AI can write almost any analytical query you can describe — but the quality of the output depends entirely on whether the AI knows your schema, your dialect, and your performance constraints. This topic shows you how to brief AI for SQL that runs the first time, on the warehouse you actually use.

1. Introduction

Most data scientists write SQL every day — for ad-hoc questions, dashboard queries, ETL transforms, and ML feature pipelines. AI is one of the highest-leverage assistants for SQL because the syntax is dense, the dialect quirks are many, and a single missing GROUP BY clause can silently change a result. A well-briefed prompt produces correct, performant, dialect-aware SQL in seconds. A vague prompt produces ANSI-flavoured guesses that won't run in BigQuery, Snowflake, or Postgres without rewriting. This tutorial gives you the patterns to get the good outcome every time.

2. The Concept Explained

SQL prompts have one extra ingredient compared to Pandas prompts: the dialect. PostgreSQL, MySQL, BigQuery, Snowflake, Redshift, Databricks SQL, and DuckDB all share roughly 80% of their syntax — and the remaining 20% is where SQL errors hide. Date functions, window frames, array handling, JSON access, and pivot syntax differ across all of them. State your dialect at the very top of the prompt and the AI will produce code that runs.

The other key element is the schema brief: table names, column names, data types, primary keys, and foreign-key relationships. SQL prompts without schemas produce queries with fabricated joins — usually almost-right joins that introduce duplicates you only notice three days later.

The reusable SQL brief

Use this structure at the top of every SQL prompt:

Dialect: BigQuery (Standard SQL).
Schema: analytics_warehouse

Tables:
  customers (
    customer_id INT64 PK,
    signup_ts TIMESTAMP,
    plan_type STRING,    -- basic | pro | enterprise
    country STRING
  )

  orders (
    order_id INT64 PK,
    customer_id INT64 FK -> customers,
    order_ts TIMESTAMP,
    status STRING,       -- placed | shipped | returned
    gross_value_gbp NUMERIC,
    discount_pct NUMERIC -- 0..0.45, nullable
  )

  events (
    event_id INT64 PK,
    customer_id INT64 FK -> customers,
    event_ts TIMESTAMP,
    event_type STRING
  )

Notes:
- orders has ~28M rows, partitioned by DATE(order_ts).
- events has ~520M rows, clustered by customer_id.
- Returns count as negative revenue per business rule.

3. The Problem Without This Technique

Write a SQL query to find my top customers by revenue.

Act as a senior analytics engineer writing BigQuery SQL.

Schema (paste reusable brief from above).

Task: identify the top 50 customers by net revenue
in the last 90 days, where net revenue per order =
gross_value_gbp * (1 - COALESCE(discount_pct, 0))
and only status = 'shipped' contributes.

Requirements:
- Use a CTE per logical step (orders_filtered,
  orders_with_net, revenue_per_customer).
- Use partition pruning: filter on order_ts
  before joining.
- Return columns: customer_id, customer_country,
  plan_type, total_net_revenue_gbp, order_count.
- Sort by total_net_revenue_gbp DESC, limit 50.
- Add a top-of-query comment block explaining
  the business definition of "net revenue".

4. The Solution

The pattern is: dialect → schema brief → task → business rule → performance notes → output structure. Dialect is non-negotiable. Schema brief eliminates fabrication. Business rule (here: "returns count as negative revenue") removes ambiguity. Performance notes (partitioning, clustering) make sure the query runs in reasonable time on real-world volumes.

For complex multi-step analyses, ask the AI to use CTEs rather than nested subqueries. The phrase "Use one CTE per logical step and name them clearly" produces queries that read top-to-bottom like a paragraph — much easier to review and modify later.

5. Step-by-Step Breakdown

1. State the dialect explicitly. Even "Postgres 15" is more useful than "PostgreSQL" — version differences in window functions and JSON handling matter.
2. Paste the schema brief. Tables, columns, types, keys, and partitioning. Save the brief once per warehouse and reuse it forever.
3. Describe the metric precisely. What counts as "active"? What counts as "revenue"? Define before asking for code.
4. Demand CTEs. One CTE per logical step turns nested chaos into readable analysis.
5. Add performance hints. Partition columns, clustering, indexes. AI will use them if it knows about them.
6. Test on a small slice first. Ask for a LIMIT 1000 version of the query before running on the full table — much cheaper iteration.

Tip: Ask AI to write the query and a brief plain-English explanation of each CTE. The explanation doubles as documentation when you commit the query to your dbt or analytics repo.

6. Practice Exercises

Rewrite this query for better performance on a partitioned, clustered warehouse. Explain each optimisation.

7. Key Takeaways