Media Kit Contact Submit an AI
Data scientist ›

🗄️ SQL Query Generation

Produce complex SQL queries (multiple joins, CTEs, analytical functions) in minutes that would take 30-60 minutes to write manually.

Data scientists and analysts spend 30 to 50% of their time writing SQL: exploration, aggregations, joins, analytical windows. Generative AI can produce in seconds queries that would take you 30-60 minutes to write and debug. The pitfall: generated SQL can be syntactically correct but semantically wrong (bad join, double counting, NULL mishandled). This guide presents the rigorous workflow that maximizes productivity while avoiding invisible errors that skew business results.

Step-by-step Workflow
1
Describe the schema to the AI

Provide the involved tables with their main columns, types, and relationships (PK/FK). Without schema, AI invents plausible but nonexistent column names. Ideally paste a DDL or table dictionary.

2
State the business question clearly

Not 'write me a SELECT', but 'for each customer active for more than 6 months, calculate cumulative revenue over the last 12 months and the percentage growth vs. the previous 12 months'. The clearer the question, the better the query.

3
Specify the target database and its specifics

Postgres, BigQuery, Snowflake, Redshift, MySQL: analytical functions and syntax differ. Specifying allows an optimized query for your dialect.

4
Test on a sample before production

Limit to WHERE date >= '2025-01-01' AND id < 1000 for quick validation. Compare with a known case (counted manually) to verify result consistency.

5
Iterate to optimize

If the query is slow: have the AI suggest optimizations (missing indexes, materialized CTEs, JOIN order). Always check the execution plan (EXPLAIN) before putting into production.

Copyable Prompts
Generation of business SQL query 
You are a SQL expert in [POSTGRES / BIGQUERY / SNOWFLAKE / REDSHIFT / MYSQL]. Here's my schema:nn[TABLES + COLUMNS + RELATIONS]nnBusiness question: [DETAILED QUESTION]nnWrite a query that:n- Uses the right JOINs (explicit on INNER vs LEFT)n- Handles NULL correctlyn- Avoids double countingn- Uses named CTEs for readabilityn- Includes comments on non-trivial choicesnnProvide: (1) the query, (2) a 3-5 line explanation of choices, (3) an expected result on a few rows for validation.
Debug of a slow query 
This query runs in [DURATION] on [VOLUME] of data:nn[QUERY]nnSchema:n[TABLES + EXISTING INDEXES]nnExecution plan:n[EXPLAIN ANALYZE OUTPUT]nnPropose:n1. **Diagnosis**: where are the bottlenecks (full scan, bad JOIN order, missing index)?n2. **3 optimizations** by expected impact order, with modified query for eachn3. **Indexes to create** if relevant (with CREATE INDEX syntax)n4. **Risks**: impact on writes, disk space, locksnnTarget: get below [DESIRED SLA].
Conversion between SQL dialects 
Convert this query from [SOURCE_DIALECT] to [TARGET_DIALECT]:nn[SOURCE QUERY]nnKeep the same business logic but adapt:n- Date functions (DATE_TRUNC, EXTRACT, etc.)n- Analytical functions (window functions)n- CTE syntaxn- Type handling (TIMESTAMP, JSON, ARRAY)n- Target dialect particulars (LATERAL, QUALIFY, etc.)nnProvide the converted query + the 3 main differences you had to handle.
Detection of semantic errors 
Audit this SQL query for SEMANTIC errors (not just syntactic):nn[QUERY]nnSchema:n[TABLES + COLUMNS + APPROXIMATE CARDINALITIES]nnExpected business question: [QUESTION]nnCheck:n1. **JOIN cardinality**: risk of double counting?n2. **NULL handling**: COUNT(col) vs COUNT(*), AVG on NULL, etc.n3. **Filters**: WHERE vs ON in LEFT JOIN, condition ordern4. **Aggregations**: GROUP BY coherent, HAVING vs WHEREn5. **Edge cases**: what if a dimension has no facts? if multiple facts per dimension?nnFor each problem: (a) affected line, (b) explanation, (c) fix.
Generation of analytical query with window functions 
For [ANALYTICAL QUESTION — ex: top 3 products per category by revenue, user ranking by month, etc.]nnSchema:n[TABLES]nnWrite a query using __window functions__ (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER...) optimal for [DIALECT].nnExplain in 3 points: (1) why window instead of correlated subquery, (2) the PARTITION BY and ORDER BY chosen, (3) expected performance. Include a result sample.
Recommended tools
Claude Opus 4.5
★ 4.9 (92) · 20 USD/mois

Claude Opus 4.5 : modèle premium d’Anthropic pour code, agents et tâches complexes en entreprise.

Why : Excellence sur le SQL complexe et les fonctions analytiques. Comprend les subtilités sémantiques mieux que les concurrents.

ChatGPT
★ 4.9 (528) · 20 USD/mois

Assistant conversationnel polyvalent d’OpenAI. Rédige, résume, code, traduit et répond à tout type de question.

Why : Solide sur tous les dialectes courants, particulièrement bon pour la conversion entre BDD.

🤖
Cursor
★ 4.8 (145) · 20 USD/mois

Éditeur de code IA révolutionnaire basé sur VS Code avec agents autonomes

Why : Si vous travaillez sur des fichiers SQL versionnés (dbt, scripts de migration), Cursor donne du contexte projet à l'IA.

Estimated ROI
Time Saved
60-70% on complex query writing
Quality Gain
Detection of semantic errors in pre-production
Cost
Included in Claude Pro / ChatGPT Plus subscription (20-30€/month)
Frequently asked questions
Does AI handle all SQL dialects well?

Postgres, MySQL, BigQuery, Snowflake, Redshift: very well. SQL Server (T-SQL): good, sometimes forgets proprietary syntaxes. Oracle (PL/SQL): correct but demands more verification. DuckDB, SQLite: good on standard SQL, sometimes confused on extensions.

Can you do SQL on sensitive data with ChatGPT?

The SQL code itself isn't sensitive — the data is. So yes, you can generate SQL queries via any LLM as long as you don't send it real customer data. Only paste schemas and dummy examples in prompts.

Can AI replace a DBA?

For routine query writing, optimization help, documentation: largely yes. For database architecture, fine DBMS tuning, high availability, backup, security: no, the DBA remains essential. AI is an excellent SQL writer, not a DBA.

Should you document that a query was AI-generated?

Good practice in collaborative environments (dbt, Airflow, versioned scripts): yes, as a comment with the query + the prompt used. This lets reviewers understand the logic and regenerate with improvements if needed.

← Back to guide Data scientist