Using AI to Write SQL: A Guide for Data Analysts
How to get AI to write SQL that matches your real schema, catch common errors like join fan-out and time zones, and use it safely with company data.
You get an urgent request: "Show the repeat purchase rate by month of first purchase, by this afternoon." You open an AI tool, type the request as is, and get 30 lines of SQL that run fine, yet the numbers are almost double what they should be. The problem is that AI does not know your data structure; it guesses something that looks reasonable. This article shows how to work with AI to get SQL that both runs and is correct.
What does using AI to write SQL mean?
It means having a large language model turn business questions into SQL, explain queries others have written, and help fix errors. Many tools now have this built in. Google Cloud documentation states that Gemini in BigQuery can generate SQL from plain-language comments, explain a query with "Explain this query", and help fix errors, with English-language prompts only.
The mechanism: why AI writes wrong SQL
AI generates SQL from language patterns it has seen. Without your schema it guesses table names, column names and relationships, producing queries that look right but have flawed logic. The fix is to give complete context.
Context to include every time
- Dialect: BigQuery, PostgreSQL, MySQL or SQL Server, because date functions differ
- Schema: table names, columns, data types and primary keys
- Relationships: is the orders table one row per bill, or one row per item in a bill?
- Business definitions: "repeat customer" means a second purchase within 90 days, excluding cancelled bills
Errors to check every time
Joins that multiply rows (fan-out)
If you join the bills table to the line-items table and then sum bill totals, each total is counted once per item. Check by counting rows before and after the join.
Time zones
Many systems store time in UTC. If you do not convert to Thai time (Asia/Bangkok), bills from midnight to 7 a.m. fall into the previous day and daily numbers drift.
NULLs and date boundaries
Filtering with "not equal to" also drops NULL rows, and a date range using "less than or equal to" the last day can miss data after midnight.
Example: repeat customers for an online shop
An analyst at a supplement brand selling through its own website gives the AI the schema of the orders and customers tables plus the repeat-purchase definition, and asks it to work in steps. First, a CTE finds each customer's first purchase date. Second, it checks for a following bill within 90 days. Finally, it groups by month of first purchase and calculates the share. She asks the AI to explain each CTE in Thai, then checks the result against 3 customers whose history she knows before sending the numbers.
SQL that runs is not the same as SQL that is right.
Watch out for
- Do not paste personal data or database passwords into AI tools your organisation has not approved. Share only the schema and masked sample data.
- Use a read-only account when running AI-written queries, to block statements that modify or delete data.
- Mind the cost in warehouses that charge by data scanned. Test on a short date range first.
- Outputs vary. Google's documentation says Gemini may suggest different syntax for the same prompt and can produce plausible but incorrect output, so always verify.
How to get started
- Create a shared context file with the dialect, schema and business definitions, and paste it every time you ask AI.
- Ask AI to write step-by-step CTEs and explain each one.
- Check row counts, totals and sample cases where you know the answer.
- Use AI as a reviewer: "Where might this query double count or drop rows?"
- Store verified queries in a team library.
If query thinking is still new to you, try the courses Business data analysis with the QUERY formula, with case studies or First steps to becoming a data analyst in the AI era on Plants.work Learn. If you are preparing for a data interview, read How to prepare for a Data Analyst interview.