You know the drill: a report is due, the data is messy, and the query you wrote at 4 PM is still running. Most analysts waste hours on trial-and-error SQL, but with the right prompts, an AI assistant can turn a 30-minute debugging session into a 30-second copy-paste. This isn't about replacing your skills—it's about automating the boring parts and focusing on the insights.
Below are ten battle-tested prompts for SQL and PostgreSQL. They cover everything from complex JOINs and window functions to query optimization and schema design. Each prompt includes a concrete example and a note on what it solves. These are the exact prompts I use in my daily workflow, refined through hundreds of iterations.
1. The "Explain Like I'm Five" Query Deconstructor
Prompt:
I have this query: [INSERT QUERY]. Break it down step by step. Explain what each clause does, what the intermediate result sets look like, and identify any potential performance bottlenecks. Use a simple analogy for each part.
Example:
You paste a gnarly correlated subquery. The AI explains it as a "nested loop of post-it notes" and flags that the subquery runs once per row, suggesting a JOIN instead. This is gold for onboarding juniors or refreshing your own memory on a query you wrote months ago.
Why it works: It forces the AI to teach, not just answer. You get a mental model, not just a fix.
2. The Window Function Wizard
Prompt:
Write a PostgreSQL query to calculate [metric] for each [category] over the last [time period], including a running total and a 7-day moving average. Use window functions (OVER, PARTITION BY, ROWS BETWEEN).
Example:
"Write a query to calculate daily revenue per product category, including a running total and a 7-day moving average, ordered by date." The AI generates:
SELECT
category,
sale_date,
daily_revenue,
SUM(daily_revenue) OVER (PARTITION BY category ORDER BY sale_date) AS running_total,
AVG(daily_revenue) OVER (PARTITION BY category ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_revenue;
Why it works: Window functions are powerful but syntactically dense. This prompt gets you a correct, efficient template in seconds.
3. The Slow Query Autopsy
Prompt:
My PostgreSQL query takes [X] seconds. Here's the EXPLAIN ANALYZE output: [PASTE]. Identify the bottleneck (seq scan, hash join, etc.) and rewrite the query or suggest an index to fix it. Explain the trade-offs.
Example:
You paste an EXPLAIN ANALYZE showing a sequential scan on a 10M-row table. The AI suggests a partial index and rewrites the WHERE clause to use it, cutting runtime from 8s to 0.2s.
Why it works: It grounds the AI in real data. You're not asking for generic advice; you're giving it the exact plan to analyze.
4. The Schema Designer
Prompt:
Design a PostgreSQL schema for [project description]. Include tables, primary keys, foreign keys, indexes, and check constraints. Use appropriate data types (e.g., UUID, JSONB, timestamptz). Also write a migration script.
Example:
"Design a schema for a simple e-commerce platform with users, orders, products, and order_items." The AI produces a normalized schema with a junction table, enums for order status, and indexes on foreign keys.
Why it works: It gives you a solid foundation in minutes, especially useful for prototyping or when you're new to a domain.
5. The Anti-JOIN Detective
Prompt:
I have two tables: [table A] and [table B]. Write a query to find all rows in A that do NOT have a matching row in B based on [column]. Compare LEFT JOIN / IS NULL vs NOT EXISTS vs NOT IN, and show the performance implications.
Example:
"Find all customers who haven't placed an order in the last 30 days." The AI returns both a LEFT JOIN and a NOT EXISTS version, explaining that NOT EXISTS is often faster when B is large.
Why it works: This is a classic interview question and a real-world gotcha. Having the trade-offs laid out prevents silent NULL-related bugs.
6. The Pivot Table Pro
Prompt:
Create a pivot table in PostgreSQL using CASE WHEN or FILTER to transform [long-format table] into a wide format. Show sales by month as columns.
Example:
"Transform monthly sales data (year, month, revenue) into a table with columns for each month (Jan, Feb, ...)." The AI writes:
SELECT
year,
SUM(CASE WHEN month = 1 THEN revenue END) AS jan,
SUM(CASE WHEN month = 2 THEN revenue END) AS feb,
...
FROM sales
GROUP BY year;
Why it works: Pivoting in SQL is verbose. This prompt generates the repetitive CASE WHEN statements instantly.
7. The CTE Chain Builder
Prompt:
Break down this complex query into a series of CTEs (WITH clauses) for readability. Each CTE should have a clear name and comment. Here's the query: [INSERT].
Example:
You paste a 20-line query with nested subqueries. The AI refactors it into with recent_orders as (...), paid_orders as (...), final as (...). This is a lifesaver for maintaining code and explaining it to others.
Why it works: It turns a spaghetti query into a readable narrative, making debugging and peer review painless.
8. The JSONB Extractor
Prompt:
I have a table with a JSONB column. Write a query to extract [specific fields] and flatten them into columns. Handle missing keys gracefully. Use the -> and ->> operators.
Example:
"Extract the 'name' and 'email' fields from a 'metadata' JSONB column, defaulting to 'N/A' if missing." The AI produces:
SELECT
id,
COALESCE(metadata->>'name', 'N/A') AS name,
COALESCE(metadata->>'email', 'N/A') AS email
FROM users;
Why it works: JSONB is flexible but tricky. This prompt saves you from writing verbose CASE WHEN statements for every field.
9. The Date/Time Tamer
Prompt:
I need to group data by [week/month/quarter] in PostgreSQL. Write a query that uses date_trunc and to_char to format the grouping label. Include timezone handling.
Example:
"Group sales by week, showing the week start date as a label." The AI generates:
SELECT
date_trunc('week', sale_date) AS week_start,
SUM(amount) AS total_sales
FROM sales
GROUP BY week_start
ORDER BY week_start;
Why it works: Date functions are notoriously easy to mess up. This prompt gets you the exact syntax and handles timezone pitfalls.
10. The Query Explainability Checker
Prompt:
Review this query for potential issues: [INSERT]. Check for implicit type casting, missing indexes, N+1 problems, and incorrect use of NULL. Suggest improvements.
Example:
You paste a query that compares a text column to a varchar parameter. The AI spots the implicit cast that prevents index usage and suggests casting the parameter instead.
Why it works: It's a code review for SQL. It catches subtle bugs that can cause data corruption or severe performance degradation.
Putting It All Together
These prompts aren't magic—they're accelerators. The key is to provide context: paste your schema, your query, your EXPLAIN ANALYZE. The more specific you are, the better the AI's response.
Start with one or two that address your biggest pain point. Integrate them into your workflow, and you'll find yourself spending more time on analysis and less on syntax. The hours you save add up quickly.
What's your go-to SQL prompt? Share it in the comments below—let's build a community collection.
Comments