ChatGPT Advanced SQL Query Builder Prompt
Write complex SQL queries for analytics and reporting — CTEs, window functions, subqueries, and performance optimisation.
Category
💻 Coding
Difficulty
Advanced
Models
3
Last Updated
2026-06-29
Works with
📄 Example output
⚠️ Common Mistakes
❓ FAQ
⚙️ Fill in your variables
📋 Prompt
You are a senior data engineer who writes production SQL for analytics teams at scale.
Database: [PostgreSQL/MySQL/BigQuery/Snowflake/SQLite]
Task: [what data to retrieve, calculate, or transform — be specific]
Table structure: [table names, key columns, relationships]
Performance: [no requirement/must be fast/optimise readability/both]
Task:
1. APPROACH: Strategy before any SQL — CTE vs subquery vs window function and why
2. COMPLETE QUERY: Clean, commented SQL — CTEs, uppercase keywords, consistent indentation
3. EXPLANATION: What each section does — line by line for complex parts
4. PERFORMANCE: What indexes help, which parts are expensive, how it scales
5. VARIATIONS: Simplified version + version for different database if relevant
6. TEST CASES: How to verify the query returns correct results
Database: [PostgreSQL/MySQL/BigQuery/Snowflake/SQLite]
Task: [what data to retrieve, calculate, or transform — be specific]
Table structure: [table names, key columns, relationships]
Performance: [no requirement/must be fast/optimise readability/both]
Task:
1. APPROACH: Strategy before any SQL — CTE vs subquery vs window function and why
2. COMPLETE QUERY: Clean, commented SQL — CTEs, uppercase keywords, consistent indentation
3. EXPLANATION: What each section does — line by line for complex parts
4. PERFORMANCE: What indexes help, which parts are expensive, how it scales
5. VARIATIONS: Simplified version + version for different database if relevant
6. TEST CASES: How to verify the query returns correct results
APPROACH: CTE 1 = active customers, CTE 2 = revenue per customer-product, CTE 3 = ROW_NUMBER() rank, final SELECT filters rank <= 3.
```sql
WITH active_customers AS (
SELECT customer_id FROM orders
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY customer_id HAVING COUNT(order_id) > 5
),
product_revenue AS (
SELECT o.customer_id, oi.product_id, p.product_name,
SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN active_customers ac ON o.customer_id = ac.customer_id
WHERE o.created_at >= NOW() - INTERVAL '90 days'
GROUP BY o.customer_id, oi.product_id, p.product_name
),
ranked AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY total_revenue DESC
) AS revenue_rank FROM product_revenue
)
SELECT customer_id, product_name, total_revenue, revenue_rank
FROM ranked WHERE revenue_rank <= 3
ORDER BY customer_id, revenue_rank;
```
INDEXES: orders(created_at, customer_id), order_items(order_id, product_id)
MOST EXPENSIVE: order_items JOIN + aggregation — early filter via active_customers JOIN reduces row count before this step.
```sql
WITH active_customers AS (
SELECT customer_id FROM orders
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY customer_id HAVING COUNT(order_id) > 5
),
product_revenue AS (
SELECT o.customer_id, oi.product_id, p.product_name,
SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN active_customers ac ON o.customer_id = ac.customer_id
WHERE o.created_at >= NOW() - INTERVAL '90 days'
GROUP BY o.customer_id, oi.product_id, p.product_name
),
ranked AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY total_revenue DESC
) AS revenue_rank FROM product_revenue
)
SELECT customer_id, product_name, total_revenue, revenue_rank
FROM ranked WHERE revenue_rank <= 3
ORDER BY customer_id, revenue_rank;
```
INDEXES: orders(created_at, customer_id), order_items(order_id, product_id)
MOST EXPENSIVE: order_items JOIN + aggregation — early filter via active_customers JOIN reduces row count before this step.
🏆
💡 Pro Tips
Best model for this prompt
DeepSeek
DeepSeek V3 / R1
CTEs for readability — break complex queries into named understandable steps
Window functions solve ranking/running total problems far more elegantly than self-joins
Filter as early as possible — reducing rows before expensive JOINs is the most impactful optimisation
Always use EXPLAIN ANALYZE in PostgreSQL to verify your query plan matches your intention
Correlated subqueries in SELECT lists — execute once per row, catastrophic on large tables
SELECT * in production — always name the columns you need
No index consideration — a perfect query on 10K rows can timeout on 1M without right indexes
HAVING instead of WHERE for row-level filters — HAVING runs after aggregation; WHERE is much faster
- CTE vs subquery?CTEs are preferred for readability. In PostgreSQL, CTEs are optimisation fences (evaluated separately), which can help or hurt. Test both for performance-critical queries; CTEs win for readability.
- Window function vs GROUP BY?GROUP BY reduces rows (one per group). Window functions retain all rows and add an aggregate/rank column. Use window functions when you need both the detail row AND an aggregate.
- Best model for SQL?DeepSeek V3 and R1 are exceptional — they reason through complex multi-step query problems systematically and explain performance implications clearly. Claude is also strong for explaining trade-offs.
- How to optimise a slow query?Step 1: EXPLAIN ANALYZE. Step 2: Look for sequential scans on large tables — add indexes. Step 3: Filter early. Step 4: Consider materialised views for complex repeated queries.