SQL is the lingua franca of data, but writing complex queries often feels like translating a novel into a language you only half-speak. You know the tables, you understand the joins, yet the query optimizer still stares back at you with an execution plan that makes your database crawl. The solution? Stop wrestling with syntax and start prompting. Modern AI tools can generate, explain, and optimize SQL, but only if you know how to ask. This article isn't about generic 'write a query' prompts—it's a collection of 15 battle-tested prompts that tackle real-world challenges, from debugging window functions to diagnosing index bloat. Whether you're a data analyst wrestling with PostgreSQL or a developer maintaining a MySQL legacy system, these prompts will save you hours and make your queries sing.
1. The JOIN Whisperer: Generating Complex JOINs with Clear Logic
Task: Generate a query that joins multiple tables with non-trivial conditions, ensuring the logic is transparent and maintainable.
Prompt:
I need a query that joins the `orders`, `customers`, and `order_items` tables. Each order belongs to a customer, and each order can have multiple items. I want to calculate the total revenue per customer, but only for orders placed in the last 90 days. Use a LEFT JOIN to include customers with no orders, and add a comment explaining each join condition.
Example Result:
SELECT
c.customer_id,
c.name,
COALESCE(SUM(oi.quantity * oi.price), 0) AS total_revenue
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.name
ORDER BY total_revenue DESC;
Why It Works: By specifying the join types and conditions, you guide the AI to produce a query that's both correct and self-documenting. The comments make it easy for future developers to understand the business logic.
2. The Performance Surgeon: Optimizing a Slow Query
Task: Take an existing query that runs slowly and optimize it using indexes, query rewriting, or better join strategies.
Prompt:
My query below runs in 12 seconds on a table with 5 million rows. Please optimize it. Explain what's slow and provide a faster version.
SELECT * FROM orders WHERE customer_id = 12345 AND order_date > '2025-01-01';
Example Result:
-- Optimized version
SELECT * FROM orders
WHERE customer_id = 12345
AND order_date > '2025-01-01'
ORDER BY order_date DESC
LIMIT 100;
Explanation: The original query might be doing a full table scan. Adding an index on (customer_id, order_date) would help. Also, using LIMIT reduces the result set. The AI can also suggest EXPLAIN ANALYZE to check the execution plan.
3. The Window Function Wizard: Calculating Running Totals and Moving Averages
Task: Use window functions to compute running totals, moving averages, or rankings without self-joins.
Prompt:
Write a PostgreSQL query that calculates a 7-day moving average of daily sales for each product. Use window functions.
Example Result:
SELECT
sale_date,
product_id,
daily_sales,
AVG(daily_sales) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales
ORDER BY product_id, sale_date;
Why It Works: The ROWS BETWEEN 6 PRECEDING AND CURRENT ROW defines the window precisely. This is a classic pattern for time-series analysis.
4. The Index Doctor: Diagnosing Missing or Redundant Indexes
Task: Analyze a table and suggest index improvements based on query patterns.
Prompt:
My PostgreSQL database has a table `orders` with columns `customer_id`, `order_date`, `status`. The most common queries filter by `customer_id` and sort by `order_date`. What indexes should I create? Show the CREATE INDEX statements.
Example Result:
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);
Explanation: A composite index on (customer_id, order_date) covers both filtering and sorting. The DESC is optional but can help with ORDER BY order_date DESC.
5. The Subquery Sapper: Rewriting Correlated Subqueries as JOINs
Task: Convert a slow correlated subquery into a more efficient JOIN or CTE.
Prompt:
This query is slow. Rewrite it using a JOIN or CTE instead of a correlated subquery.
SELECT name, (SELECT MAX(order_date) FROM orders WHERE orders.customer_id = customers.id) AS last_order
FROM customers;
Example Result:
WITH last_orders AS (
SELECT customer_id, MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id
)
SELECT c.name, lo.last_order
FROM customers c
LEFT JOIN last_orders lo ON c.id = lo.customer_id;
Why It Works: The CTE materializes the max order dates once, avoiding repeated subquery execution.
6. The CTE Commander: Building Readable Multi-Step Queries
Task: Break a complex query into Common Table Expressions for readability and maintainability.
Prompt:
Write a query that calculates the percentage of revenue contributed by each product category, but only for categories with at least 10 orders. Use CTEs to make it readable.
Example Result:
WITH category_revenue AS (
SELECT
c.category_name,
SUM(oi.quantity * oi.price) AS revenue,
COUNT(DISTINCT o.order_id) AS order_count
FROM categories c
JOIN products p ON c.category_id = p.category_id
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
GROUP BY c.category_name
),
total_revenue AS (
SELECT SUM(revenue) AS total FROM category_revenue
)
SELECT
cr.category_name,
cr.revenue,
(cr.revenue / tr.total) * 100 AS pct_revenue
FROM category_revenue cr
CROSS JOIN total_revenue tr
WHERE cr.order_count >= 10
ORDER BY pct_revenue DESC;
7. The Database Whisperer: Explaining Execution Plans
Task: Get a clear, human-readable explanation of an execution plan and identify bottlenecks.
Prompt:
I ran EXPLAIN ANALYZE on this query and got the following output. Explain what it means and suggest improvements.
[Paste EXPLAIN output]
Example Result: The AI would interpret the plan, highlight a sequential scan on a large table, recommend an index, and explain the cost estimates.
8. The ETL Engineer: Generating Data Cleansing Queries
Task: Write queries to clean and transform data, such as removing duplicates, standardizing formats, or filling NULLs.
Prompt:
Write a PostgreSQL query to remove duplicate rows from the `users` table based on the `email` column, keeping the row with the latest `created_at`.
Example Result:
DELETE FROM users
WHERE id NOT IN (
SELECT DISTINCT ON (email) id
FROM users
ORDER BY email, created_at DESC
);
9. The Schema Architect: Designing a Database Schema from Requirements
Task: Generate a normalized schema for a given use case, including tables, columns, and indexes.
Prompt:
Design a MySQL schema for an e-commerce platform with products, categories, customers, orders, and order_items. Include primary keys, foreign keys, and appropriate indexes. Provide the CREATE TABLE statements.
Example Result: A complete set of CREATE TABLE statements with AUTO_INCREMENT, FOREIGN KEY constraints, and INDEX definitions.
10. The Cross-Dialect Translator: Converting Between PostgreSQL and MySQL
Task: Convert a query written for one dialect to another, handling syntax and function differences.
Prompt:
Convert this PostgreSQL query to MySQL:
SELECT * FROM users WHERE created_at >= NOW() - INTERVAL '7 days';
Example Result:
SELECT * FROM users WHERE created_at >= NOW() - INTERVAL 7 DAY;
11. The Performance Tester: Generating Benchmark Queries
Task: Create a set of queries to test database performance under load.
Prompt:
Generate 5 queries that stress-test a MySQL database with a table of 10 million rows. They should cover different access patterns: point lookup, range scan, aggregation, join, and sort.
Example Result: For each pattern, a query is provided with comments explaining the stress point.
12. The Query Explainer: Demystifying Advanced SQL Concepts
Task: Explain a complex SQL concept or syntax in simple terms with examples.
Prompt:
Explain how `LATERAL JOIN` works in PostgreSQL and show a real-world example where it's useful.
Example Result: A clear explanation with a code example, such as using LATERAL to join a table to a set of rows generated by a subquery that references the outer query.
13. The Pattern Matcher: Finding Anti-Patterns in SQL
Task: Review a given SQL codebase for common anti-patterns and suggest fixes.
Prompt:
Here is a snippet from my codebase. Identify any SQL anti-patterns (SELECT *, N+1 queries, non-SARGable predicates, etc.) and suggest improvements.
[Paste SQL code]
Example Result: The AI points out SELECT *, recommends explicit columns, suggests an index for a WHERE clause with a function, and so on.
14. The Date Time Magician: Handling Time Zones and Date Arithmetic
Task: Write queries that correctly handle time zones, date ranges, and formatting.
Prompt:
I have a table `events` with a `timestamp` column in UTC. I need to report events in the 'America/New_York' time zone for the last 7 days. Write a PostgreSQL query.
Example Result:
SELECT *
FROM events
WHERE timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York'
>= (CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York') - INTERVAL '7 days';
15. The Query Tuning Studio: Analyzing and Improving a Slow Query with Indexes
Task: Use EXPLAIN to analyze a query and propose index changes.
Prompt:
The following query is slow. Use EXPLAIN to analyze it and suggest index improvements.
SELECT *
FROM orders
WHERE status = 'pending'
AND order_date > '2025-01-01'
ORDER BY order_date DESC;
Example Result: The AI explains the execution plan, notes a full table scan, and suggests a composite index on (status, order_date). It also recommends using EXPLAIN ANALYZE for real timing.
Final Thoughts
These prompts are starting points, not magic bullets. The real skill lies in understanding the generated SQL and adapting it to your specific environment. Remember to always test with EXPLAIN and consider your data distribution. As you work with these, you'll develop an intuition for what makes a good prompt—clear context, specific requirements, and a well-defined output. The next time you're stuck on a slow query, don't just stare at the screen—ask your AI assistant for a fresh perspective. Your database will thank you.
For more insights, check the official documentation for PostgreSQL and MySQL.
Comments