You know that feeling when a query that ran in 200ms suddenly takes 12 seconds after a data dump? Or when the analytics team asks for a cohort analysis and you realize you've been writing the same window function for the third time this week? SQL is the lingua franca of data, but even fluent speakers hit walls. This isn't another list of generic tips—it's a collection of prompts I actually use when I'm stuck, refactoring, or building something new. Each one is paired with a real scenario, so you can steal them, adapt them, and make them yours.
1. The Query Explainer: From 'Why Slow?' to 'Fix It' in One Shot
When to use: When a query is slower than a snail on a treadmill and EXPLAIN ANALYZE output looks like hieroglyphics.
The prompt:
Analyze the following PostgreSQL query and its EXPLAIN ANALYZE output. Identify the bottleneck (seq scan, missing index, join order, etc.). Suggest specific fixes, including DDL statements, and explain the expected performance gain.
Query: [PASTE YOUR QUERY]
EXPLAIN ANALYZE output: [PASTE THE OUTPUT]
Why it works: It forces the model to ground its advice in the actual execution plan, not guesswork. You get concrete CREATE INDEX or ANALYZE commands, and you learn why the planner chose a certain path.
Real-world example: I had a query joining orders and customers that ran in 8 seconds. The prompt pointed out a seq scan on orders for a WHERE status = 'paid' filter. The fix—a partial index CREATE INDEX idx_orders_paid ON orders (customer_id) WHERE status = 'paid';—cut it down to 300ms. The model even explained that the planner would use the index because the condition exactly matched the predicate.
2. The Refactoring Surgeon: Turning Procedural Spaghetti into Set-Based Logic
When to use: When you inherit a query that uses cursors, loops, or multiple CTEs that could be a single JOIN or window function.
The prompt:
Refactor the following SQL query to be more idiomatic and performant. Replace procedural constructs (cursors, loops) with set-based operations. Preserve the exact same output. Explain each change and why it's better.
Query: [PASTE THE QUERY]
Why it works: SQL is declarative; the engine knows how to optimize sets, not loops. This prompt nudges the model to think in sets and often reveals elegant window-function solutions.
Real-world example: A colleague had a loop that calculated running totals for each customer. The refactored version used SUM() OVER (PARTITION BY customer_id ORDER BY order_date)—a single pass instead of N queries. The model explained that window functions avoid context switching and reduce I/O.
3. The Index Whisperer: Asking for the Right Indexes
When to use: When you're designing a schema or need to optimize a specific query pattern, but you're not sure which indexes to create.
The prompt:
Given the following table schema and the most common query patterns, recommend a set of indexes. For each index, specify the columns, index type (B-tree, hash, GIN, etc.), and whether it should be a partial or covering index. Justify each recommendation.
Table definitions: [PASTE SCHEMA]
Common queries: [PASTE QUERIES]
Why it works: It forces you to think about access patterns, not just the schema. The model will suggest composite indexes for multi-column filters, partial indexes for hot subsets, and GIN indexes for JSONB fields.
Real-world example: For an events table with a metadata JSONB column, the model recommended a GIN index for WHERE metadata @> '{"type": "click"}' queries, and a composite B-tree on (user_id, created_at) for the common time-series query. The result: query time dropped from 2.5s to 40ms.
4. The Window Function Wizard: Complex Calculations Without Subqueries
When to use: When you need running totals, moving averages, or percentiles, and you're tempted to write a self-join.
The prompt:
Write a SQL query that calculates the following metric using window functions: [describe the metric, e.g., "3-day moving average of revenue per user"]. Include the complete query and explain how the window frame works.
Table schema: [PASTE SCHEMA]
Metric: [DESCRIBE METRIC]
Why it works: Window functions are often underused. This prompt gets you a clean, performant query and teaches you the syntax (e.g., ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).
Real-world example: I needed a 7-day moving average of signups per region. The model produced:
SELECT
date,
region,
AVG(signups) OVER (PARTITION BY region ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM daily_signups;
It also explained the frame, so I could tweak it to RANGE if I wanted to handle missing dates.
5. The BigQuery Cost-Cutter: Optimizing Queries for Speed and Budget
When to use: When you're on BigQuery and your queries are scanning too much data, or you're not sure if you're using partitioning/clustering effectively.
The prompt:
I have a BigQuery table with the following schema and partitioning/clustering setup. My query [paste query] scans [X] GB. How can I reduce the data scanned and the cost? Suggest schema changes (e.g., partitioning by date, clustering by user_id) and query rewrites (e.g., using WHERE on partition columns).
Table schema: [PASTE SCHEMA]
Partitioning: [e.g., ingestion_time]
Clustering: [e.g., none]
Query: [PASTE QUERY]
Why it works: BigQuery pricing is based on bytes processed. The model will push you to filter on partition columns, use SELECT only needed columns, and avoid SELECT *.
Real-world example: Our analytics team was querying a 1TB table daily, scanning all of it. The prompt suggested partitioning by event_date and rewriting the WHERE clause to filter on that column. Data scanned dropped to 20GB—a 98% cost reduction.
6. The Data Profiler: Understanding What's in Your Tables
When to use: When you inherit a database and need to understand the data distribution, nulls, and anomalies before writing queries.
The prompt:
Write SQL queries to profile the following tables: count of rows, distinct values per column, null percentage, min/max/avg for numeric columns, and top 5 most frequent values for categorical columns. Return the queries and a summary of what each query reveals.
Tables: [LIST TABLES]
Why it works: It gives you a toolkit for data exploration and helps you spot dirty data (e.g., nulls where you didn't expect them) before it breaks your analysis.
Real-world example: I ran these on a users table and found that phone was 80% null, and country had inconsistent casing (USA, usa, Usa). This prompted a data cleaning initiative.
7. The CTE Composer: Building Complex Queries Step by Step
When to use: When you have a multi-step analysis that requires several transformations, and you want to keep the query readable and modular.
The prompt:
Help me write a SQL query that does the following: [describe the steps]. Use Common Table Expressions (CTEs) to break down the logic. Show the final query and explain each CTE.
Steps: [LIST STEPS]
Why it works: CTEs make complex queries a series of logical blocks, which is easier to debug and modify. The model will structure the query cleanly.
Real-world example: I needed to compare current month revenue vs. previous month per category. The model created CTEs for current_month, previous_month, and then a final JOIN with the difference and percentage change. Readable and maintainable.
8. The Joiner's Helper: Solving Join Puzzles (and Avoiding Cartesian Explosions)
When to use: When you're not sure how to join two tables correctly, or when a join returns too many rows (a classic data explosion).
The prompt:
I have two tables, [table A] and [table B], with the following keys: [describe keys]. I want to [describe the desired result]. Write the correct JOIN (INNER, LEFT, FULL, etc.) and include any necessary conditions to avoid duplicate rows. Explain why this join is correct.
Table A columns: [LIST]
Table B columns: [LIST]
Desired result: [DESCRIBE]
Why it works: It forces the model to think about the relationship (1:1, 1:many) and the grain of the result. It often reveals that you need a DISTINCT or a GROUP BY before joining.
Real-world example: I joined orders and order_items and got duplicate orders because of the one-to-many relationship. The model suggested aggregating order_items first (e.g., SUM(quantity)) and then joining, which gave the correct totals.
9. The Performance Auditor: A Second Pair of Eyes on Your Query
When to use: When you're about to deploy a query to production and want a sanity check.
The prompt:
Review the following SQL query for performance issues. Check for: missing WHERE clauses, functions on indexed columns, implicit type conversions, and potential lock contention. Suggest improvements.
Query: [PASTE QUERY]
Why it works: It's like a code review for SQL. The model will catch common anti-patterns like WHERE YEAR(date) = 2026 (which prevents index usage) and suggest WHERE date >= '2026-01-01' AND date < '2027-01-01'.
Real-world example: The model flagged a query that used LOWER(email) in the WHERE clause, which killed the index. The fix was to store emails in lowercase or use a functional index.
10. The Schema Designer: From Requirements to Normalized Tables
When to use: When you're starting a new project and need to design a database schema from scratch.
The prompt:
Design a PostgreSQL schema for the following application: [describe the app, entities, and relationships]. Include table definitions with data types, primary/foreign keys, indexes, and constraints. Also suggest whether to use a relational or NoSQL approach, and justify your choice.
Application description: [PASTE DESCRIPTION]
Why it works: It gives you a solid starting point, and you can iterate on it. The model will normalize the schema appropriately and add useful constraints (e.g., CHECK for positive values).
Real-world example: For a simple e-commerce app, the model designed tables for users, products, orders, and order_items, with proper foreign keys and indexes on user_id and product_id. It even suggested a status enum with a check constraint.
Final Thoughts
These prompts aren't magic bullets—they're starting points. The real skill is knowing when to use them. As you work with them, you'll notice patterns: the model excels at translating vague requirements into concrete SQL, but it's up to you to verify the logic against your data. Always test on a staging environment first, and keep your EXPLAIN ANALYZE handy. Now go make your database queries sing—or at least run under 100ms.
If you're hungry for more, check out our other posts on AI-assisted development, from Python to Terraform. And if you have a prompt that saved your day, share it in the comments below—we're always collecting war stories.
Comments