AI Prompts for SQL Query Writing

The Problem Elena Faced

Most SQL tutorials teach you to write queries. Nobody teaches you to write queries that will still perform in two years when your table has 50 million rows instead of 50,000.

Elena is a data analyst at a retail company. She's not a database expert — she knows SQL well enough to get the data she needs, and that's usually good enough. For two years it was fine. Then the company had a good Q4. User signups tripled. And suddenly her "orders by region" report, which used to run in 4 seconds, started timing out.

She'd been using AI for SQL for about six months. Her standard prompt: "Write a SQL query to find X." It worked great — until the queries that "worked" started failing at scale.

Her prompt problem wasn't that AI gave her wrong SQL. It was that she was asking for correct SQL and getting it. What she actually needed was efficient SQL — and those are different things.

The Wrong Approach

Elena's typical prompt:

Write a SQL query to find total orders by region for the last 30 days, grouped by region and payment method.

Tables: orders (id, customer_id, region, payment_method, amount, created_at, status)

The AI wrote a clean, correct query. GROUP BY with a date filter. She ran it. It returned the right data. She used it in her dashboard.

At 50K rows: 4 seconds. At 2M rows: 28 seconds. At 15M rows: timeout.

The query was doing a full table scan every time it ran. The date filter on

created_at

⚠️ Common mistake: Asking AI to write "a SQL query" without specifying that it should be production-ready and performant. AI defaults to correctness, not efficiency. Performance is a separate requirement you have to state.

The Correct Prompt

Write a production-ready PostgreSQL query for the following requirement.

Requirement: Find total order count and total revenue by region and payment method for the last 30 days.

Table: orders
Columns: id (bigint, PK), customer_id (bigint), region (varchar), payment_method (varchar), amount (decimal), created_at (timestamp), status (varchar)
Existing indexes: idx_orders_customer_id on (customer_id), idx_orders_created_at on (created_at)

Table size context: ~15 million rows, growing by ~200K rows/month

Requirements:
1. Query must be performant at current table size and 10× current size
2. Filter: status = 'completed' and created_at in last 30 days
3. Group by: region, payment_method
4. Return: region, payment_method, order_count, total_revenue, avg_order_value

After the query:
- Explain the execution plan (what operations PostgreSQL will perform)
- Identify any potential bottleneck as data scales
- Suggest any additional indexes that would improve performance
- Suggest if a materialized view or pre-aggregation would be appropriate for a dashboard use case

What this does: Shifts the AI from "write correct SQL" to "write production-ready SQL" by providing table size context, existing indexes, and explicitly asking for the performance analysis. The AI now has everything it needs to make indexing recommendations and flag scaling concerns.

⚡ Pro tip: Always tell the AI your existing indexes. Without this information, it can't know whether its recommended query will use an index or force a sequential scan. Index-unaware queries look fine in the output and fail in production.

Results and What Changed

The AI's response to the improved prompt included three things Elena's original approach never got:

A composite index recommendation:

CREATE INDEX idx_orders_dashboard ON orders (status, created_at, region, payment_method) INCLUDE (amount)

A materialized view suggestion: For a dashboard refreshed every hour, a materialized view refreshed on a schedule would be far more efficient than running the aggregation query live on each page load.

A scaling warning: At 150M rows, even the optimized query would slow down, and the recommendation was to partition the orders table by month — something that's much easier to plan for than to retrofit.

The optimized query with the new index ran in 0.3 seconds at 15M rows. More importantly, Elena now had a framework for thinking about query performance — not just a faster query.

How to Apply This to Your Situation

The "table size + existing indexes + scale requirement" framework improves any SQL prompt. Here are three variations for common scenarios:

For complex JOIN queries:

Write a PostgreSQL query joining these tables: [list tables and relationships]. Table sizes: [list approximate row counts]. Existing indexes: [list]. Optimize for read performance. After the query, explain the join order you'd recommend and why, and flag any potential Cartesian product risks.

What this does: Addresses the two most common JOIN performance problems — wrong join order and accidental Cartesian products from missing join conditions.

For subquery vs CTE decisions:

I need to write a query that [describe requirement]. It could use either a subquery or a CTE. Write both versions. Explain which PostgreSQL will execute more efficiently for a table of [size] rows, and why. If a window function would be more appropriate than either, show that version too.

What this does: Gets you an explicit comparison of query patterns instead of letting the AI pick one arbitrarily — which helps you learn the trade-offs rather than just copy a solution.

For analytical queries (OLAP-style):

Write a PostgreSQL query for this analytical requirement: [describe]. This query runs on a reporting database (not the primary transactional DB). Optimize for throughput over latency — it's acceptable for this to take 30 seconds as a batch job. Prefer readability over micro-optimizations.

What this does: Changes the optimization target. OLAP queries have different trade-offs than OLTP queries — specifying the context gets you appropriate design choices.

⚡ Pro tip: After getting a query, ask: "Generate EXPLAIN ANALYZE output for this query assuming the indexes I've described exist. Walk through each node in the execution plan." You won't get real execution statistics, but the walkthrough helps you understand what the planner will do — and catches cases where the AI's indexing assumptions are wrong.

Next Steps

Elena now uses a standard SQL prompt template that always includes table sizes, existing indexes, and a scale requirement. She's also started saving her best-performing query patterns to a shared prompt library using PromptABCD — so when a similar reporting requirement comes up, the starting point is a tuned ai prompts sql queries template, not a blank page.

The shift from "write me a query" to "write me a production-ready query and explain why it'll perform" is the difference between SQL that works in development and SQL that works in production. It takes an extra 30 seconds to write the prompt. It saves hours of performance debugging later.

SQL Prompt Patterns for Specific Scenarios

A few high-value scenario-specific SQL prompts worth having in your toolkit:

For window function queries:

Write a PostgreSQL query using window functions to calculate: [requirement]. Explain what the PARTITION BY and ORDER BY clauses do in plain English, and why a window function is more appropriate than a subquery or GROUP BY for this use case.

What this does: Window functions are powerful but confusing. Asking for a plain-English explanation alongside the SQL builds understanding faster than any tutorial.

For recursive CTEs:

Write a recursive CTE to traverse this hierarchical data: [describe structure and query goal]. Walk me through how the recursion terminates and what would happen if there were a circular reference in the data.

What this does: Recursive CTEs have a subtle infinite-loop risk when data has cycles. Asking explicitly about termination conditions and circular reference handling gets you safe recursive queries.

For migration from one SQL dialect to another:

Convert this MySQL query to PostgreSQL-compatible SQL. Flag any MySQL-specific syntax that has no direct equivalent in PostgreSQL, and suggest the closest PostgreSQL alternative with any behavioral differences noted.

What this does: SQL dialect differences — especially around date functions, string operations, and JSON handling — cause subtle bugs when migrating databases. Getting a flagged diff is faster than discovering them in testing.

⚡ Pro tip: When asking AI to optimize an existing slow query, always paste the actual EXPLAIN ANALYZE output if you can get it. "This query shows Seq Scan on orders (cost=0.00..450000.00)" tells the AI exactly what the planner is doing wrong — and gets you a much more targeted optimization than describing the slowness in words.

Collecting your best ai prompts sql queries in a shared library pays off over a surprisingly short time horizon. A team of five analysts, each running two SQL sessions a day with well-structured prompts, produces noticeably better query quality than the same team using ad-hoc requests — and the institutional knowledge accumulates in the prompt library rather than individual tribal knowledge.

SQL written with production context produces production-quality results. The habit of including table sizes and existing indexes in every SQL prompt is one of those small changes that accumulates into a dramatically better codebase over a year of development.

⚡ Pro tip: Ask the AI to generate database seed data alongside your schema: 'Write a SQL seed script that populates this schema with realistic test data — 100 users, 500 orders, 1000 order items. Use realistic value distributions (most orders should be small, a few large), not random values.' Realistic seed data catches data distribution assumptions in queries that synthetic data misses.

Schema design is one of those rare areas