You know that feeling when you're staring at a 200-line SQL query, trying to figure out why it takes 30 seconds to run, and your coffee has gone cold? Or when you need to write a complex window function but can't remember the exact syntax? If you're a developer or data analyst, you've been there. What if you could just describe what you need in plain English and get a working, optimized query in seconds? That's exactly what AI prompts can do for you. In this guide, I've compiled 15 battle-tested prompts for PostgreSQL, analytics, and query optimization that I use in my own workflow. Each one comes with a real example and an explanation of the problem it solves. No fluff, just practical value.
1. The "Explain Like I'm 5" Query Explainer
Prompt: "Explain the following PostgreSQL query step by step, as if I'm a beginner. For each part, tell me what it does, what it returns, and how it affects performance. Query: SELECT c.customer_id, COUNT(o.order_id) AS order_count FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.order_date >= '2025-01-01' GROUP BY c.customer_id HAVING COUNT(o.order_id) > 5;"
Why it works: This prompt forces the AI to break down complex SQL into digestible chunks, which is great for learning or for reviewing code you didn't write.
Example output: The AI will explain the LEFT JOIN, the GROUP BY, the HAVING clause, and why the condition on order_date is in the JOIN rather than the WHERE (to avoid filtering out customers with no orders). It will also note that this query might benefit from an index on orders(order_date, customer_id).
2. The Query Optimizer (Index Tuning)
Prompt: "I have a PostgreSQL query that runs slowly. Here's the query and the result of EXPLAIN ANALYZE: [paste query and EXPLAIN output]. Identify the bottlenecks, suggest indexes, and rewrite the query if necessary. Provide the revised query and the new EXPLAIN ANALYZE output."
Why it works: This prompt leverages the AI's ability to read execution plans and suggest concrete improvements, turning you into a performance-tuning wizard.
Example: You paste a query that does a sequential scan on a 10-million-row table. The AI suggests creating a composite index on (user_id, created_at) and might rewrite a correlated subquery into a JOIN, cutting execution time from 1.2s to 40ms.
3. Window Function Wizard
Prompt: "Write a PostgreSQL query that calculates the running total of sales per region, ordered by date, and also returns the previous month's sales for each row. Here are the tables: sales(id, region, sale_date, amount). Provide the query and explain how the window functions work."
Why it works: Window functions are powerful but tricky; this prompt gets you a correct query and a clear explanation, saving you from trial-and-error.
Example Output:
SELECT
region,
sale_date,
amount,
SUM(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
LAG(amount, 1) OVER (PARTITION BY region ORDER BY sale_date) AS prev_month_sales
FROM sales
ORDER BY region, sale_date;
4. The "Convert English to SQL" Translator
Prompt: "Convert the following business question into a PostgreSQL query: 'Show me the top 5 products by revenue in the last quarter, along with the number of units sold, but only for products that have at least 10 reviews.' Tables: products(id, name, price), order_items(order_id, product_id, quantity), reviews(product_id, rating), orders(id, created_at)."
Why it works: This is the most common use case: you know what you want, but you don't know (or remember) the exact SQL syntax. The AI produces a query that you can then test.
Example Output: A query joining orders, order_items, and products, filtering by date, grouping, and using HAVING COUNT(review_id) >= 10.
5. The Data Cleaning Query Builder
Prompt: "I have a table called 'customers' with a messy 'phone' column that contains formats like '(123) 456-7890', '123-456-7890', and '1234567890'. Write a PostgreSQL query using regex to normalize this into a standard format like '+1-123-456-7890'. Also, find and list rows where 'email' is not a valid format."
Why it works: Data cleaning is tedious, but AI can generate regex-heavy queries instantly, saving you from countless regex tutorials.
Example Output: Using regexp_replace with patterns, and a WHERE email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$' to find invalid emails.
6. The "Why Is This Query Slow?" Diagnostic
Prompt: "I have a query that used to run fast but now takes 5 seconds. The table has grown to 50 million rows. Here's the query: [paste]. Here's the EXPLAIN output: [paste]. What could be the cause? Suggest specific actions like VACUUM, ANALYZE, index rebuild, or query rewrite."
Why it works: This prompt helps you diagnose performance regressions systematically, giving you a prioritized action list.
Example Output: The AI might point out that your indexes are bloated due to frequent updates, recommend running VACUUM ANALYZE, or suggest a partial index for a common filter.
7. The "Fix My Broken Query" Debugger
Prompt: "I'm getting an error in PostgreSQL: 'ERROR: column 'id' does not exist'. Here's my query: [paste]. Can you fix it and explain what was wrong?"
Why it works: Instead of aimlessly googling errors, you get an instant fix and explanation.
Example: The AI might spot a typo or a missing table alias, and correct it, explaining that id needed to be qualified as users.id because of a JOIN.
8. The Query Rewriter (From Subqueries to JOINs)
Prompt: "Rewrite the following PostgreSQL query to use JOINs instead of subqueries, if it improves readability or performance. Also, explain the difference in execution. Query: SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE signup_date > '2025-01-01');"
Why it works: Sometimes rewriting a query can improve performance, and AI can show you the trade-offs.
Example Output: A version using JOIN customers ON ..., and a note that in modern PostgreSQL, the planner often optimizes IN-subqueries to the same plan, but JOINs might be clearer.
9. The "Generate Test Data" Helper
Prompt: "Generate a PostgreSQL script that creates a table 'events' with columns (event_id serial, event_name text, occurred_at timestamptz, user_id int) and inserts 10,000 random rows with realistic data (e.g., event names like 'click', 'purchase', 'view' with probabilities). Use generate_series and random functions."
Why it works: Testing queries often requires dummy data; this prompt produces a script that saves you hours of manual INSERTs.
Example Output: A script using generate_series(1,10000), random(), and now() - (random() * interval '30 days').
10. The "Find Duplicates" Detective
Prompt: "Write a PostgreSQL query to find duplicate email addresses in a 'users' table, and show the count of duplicates. Also, provide a query to delete all but the oldest duplicate (keep the one with the smallest id)."
Why it works: Duplicate detection and removal is a common task, and AI provides both the analysis and the cleanup query.
Example Output: Using GROUP BY email HAVING COUNT(*) > 1, and a DELETE using a subquery with ROW_NUMBER().
11. The "Date/Time Series" Specialist
Prompt: "Write a PostgreSQL query that returns the number of orders per day for the last 30 days, including days with zero orders. Use generate_series to fill gaps. Tables: orders(id, created_at timestamptz). Provide the query and explain how generate_series works."
Why it works: Time-series analysis often needs gap filling, which is non-trivial; this prompt gives you a template.
Example Output:
SELECT d.date, COUNT(o.id) AS order_count
FROM generate_series(CURRENT_DATE - INTERVAL '29 days', CURRENT_DATE, '1 day') AS d(date)
LEFT JOIN orders o ON o.created_at::date = d.date
GROUP BY d.date
ORDER BY d.date;
12. The "Recursive CTE" Generator
Prompt: "Write a PostgreSQL recursive CTE that generates a Fibonacci sequence up to the 20th term. Explain how the recursive part works."
Why it works: Recursive CTEs are hard to write from memory; this prompt gives you a working example you can adapt for hierarchical data.
Example Output: A CTE with UNION ALL, using n-1 and n-2 to compute each term.
13. The "JSONB" Query Pro
Prompt: "I have a 'users' table with a JSONB column 'metadata' that stores things like 'age', 'city'. Write PostgreSQL queries to (1) find users with age > 30, (2) get the distinct cities, and (3) update the age for a specific user. Use jsonb operators like @>, ->>, and ||."
Why it works: JSONB queries have a learning curve; this prompt gives you ready-to-use examples for common operations.
Example Output: Queries using metadata @> '{"age": 30}', metadata->>'city', and update with || to merge objects.
14. The "Pivot Table" Builder
Prompt: "Write a PostgreSQL query using the crosstab function to create a pivot table showing total sales per product per month, where months are columns (Jan, Feb, Mar). Tables: sales(product, month, amount). Provide the query and explain how to install the tablefunc extension."
Why it works: Pivoting is complex; this prompt gives you the exact syntax and setup steps.
Example Output: A query using crosstab with CREATE EXTENSION IF NOT EXISTS tablefunc; and a clear explanation of the function arguments.
15. The "Explain the Execution Plan" Guru
Prompt: "Here's an EXPLAIN ANALYZE output for a PostgreSQL query: [paste]. For each node, explain what operation it performs (Seq Scan, Index Scan, Hash Join, etc.), and point out any red flags like high 'rows removed by filter' or high 'actual time'. Suggest improvements."
Why it works: Reading execution plans is a key skill; this prompt acts as a mentor, teaching you to spot inefficiencies.
Example: The AI might point out a sequential scan on a large table and recommend an index, or note that a sort could be avoided by using an index.
Wrapping Up
These prompts are the ones I reach for daily—they've turned me from a SQL-scripter into a query-architect. The best part? You can adapt them to your own databases and pain points. Next time you're stuck on a query, don't stare at the screen—open your AI assistant and describe the problem in plain English. You might be surprised how often the solution is just a prompt away. And if you want to take your skills further, consider exploring more advanced topics like query tuning or data modeling. Happy querying!
Comments