SQL
Write and Optimise a SQL Query
Turn a reporting question into correct SQL, then optimise it with indexes and an execution plan review.
The Prompt
write-optimised-sql-query.md
Act as a database engineer fluent in {{database}}. Schema (tables, columns, keys, approximate row counts): {{schema}} Question to answer with data: {{question}} Performance constraints: {{constraints}} Provide: 1. The query, formatted and commented 2. A plain-language explanation of how it produces the answer 3. Edge cases: nulls, duplicates, time zones, soft deletes 4. Expected execution plan shape and where the cost sits 5. Index recommendations, with the trade-off on writes 6. An alternative formulation (CTE vs subquery vs window function) and when it would be faster 7. How to verify correctness with a small sanity-check query Use only tables and columns present in the schema. If something is missing, ask instead of assuming.
Negative prompt
Included when you copy the prompt
Do not invent tables or columns that are not in {{schema}}. Do not invent indexes as if they already exist - recommend them separately. Do not return unformatted one-line SQL. Do not ignore soft deletes, nulls, or time zones when the schema hints at them. Do not recommend SELECT * for reporting queries. Do not claim an execution plan without tying cost to joins, filters, or sorts present in the query.
Fill in your details below - this prompt updates as you type, then copy it.
Fill in your details
0 of 4 filled. Preview updates as you type - then copy the finished prompt (negative prompt included when present).
write-optimised-sql-query.md
Prompt preview
Act as a database engineer fluent in {{database}}. Schema (tables, columns, keys, approximate row counts): {{schema}} Question to answer with data: {{question}} Performance constraints: {{constraints}} Provide: 1. The query, formatted and commented 2. A plain-language explanation of how it produces the answer 3. Edge cases: nulls, duplicates, time zones, soft deletes 4. Expected execution plan shape and where the cost sits 5. Index recommendations, with the trade-off on writes 6. An alternative formulation (CTE vs subquery vs window function) and when it would be faster 7. How to verify correctness with a small sanity-check query Use only tables and columns present in the schema. If something is missing, ask instead of assuming.
Examples
Example input
Database: PostgreSQL 16 Schema: orders(id uuid PK, customer_id uuid, status text, total_cents int, created_at timestamptz, deleted_at timestamptz) ~5M rows order_items(id uuid PK, order_id uuid FK, product_id uuid, qty int, price_cents int) ~20M rows Question: Monthly revenue (sum of total_cents) for paid orders in 2025, excluding soft-deleted orders. Constraints: Dashboard widget; p95 under 500ms.
What you should get
1. Query: ```sql -- Monthly paid revenue for 2025, excluding soft-deleted orders SELECT date_trunc('month', created_at) AS month, SUM(total_cents) AS revenue_cents FROM orders WHERE deleted_at IS NULL AND status = 'paid' AND created_at >= '2025-01-01' AND created_at < '2026-01-01' GROUP BY 1 ORDER BY 1; ``` 2. Filters to paid, non-deleted rows in 2025, truncates to month, sums totals. 3. Edge cases: NULL deleted_at means active; duplicate months won't appear; timestamptz uses DB timezone - pin session TZ if finance needs UTC. 4. Plan shape: Index range scan on (status, created_at) or partial index, then HashAggregate. Cost sits in the range scan + aggregate if unindexed. 5. Index: CREATE INDEX ON orders (created_at) WHERE deleted_at IS NULL AND status = 'paid'; - faster reads, slightly slower updates to status/deleted_at. 6. Alternative: daily CTE then roll up - useful if you also need day grain; otherwise monthly GROUP BY is enough. 7. Sanity check: SUM(total_cents) for January alone vs the January bucket from the monthly query.
How to get a good result
Done when
1. Query: ```sql -- Monthly paid revenue for 2025, excluding soft-deleted orders SELECT date_trunc('month', created_at) AS month, SUM(total_cents) AS revenue_cents FROM orders WHERE deleted_at IS NULL AND status = 'paid' AND created_at >= '2025-01-01' AND created_at < '2026-01-01' GROUP BY 1 ORDER BY 1; ``` 2. Filters to paid, non-deleted rows in 2025, truncates to month, sums totals. 3. Edge cases: NULL deleted_at means active; duplicate months won't appear; timestamptz uses DB timezone - pin session TZ if finance needs UTC. 4. Plan shape: Index range scan on (status, created_at) or partial index, then HashAggregate. Cost sits in the range scan + aggregate if unindexed. 5. Index: CREATE INDEX ON orders (created_at) WHERE deleted_at IS NULL AND status = 'paid'; - faster reads, slightly slower updates to status/deleted_at. 6. Alternative: daily CTE then roll up - useful if you also need day grain; otherwise monthly GROUP BY is enough. 7. Sanity check: SUM(total_cents) for January alone vs the January bucket from the monthly query.
FAQ
- What do I fill in before I copy this prompt?
- Replace these placeholders with your details: database, schema, question, constraints. Required fields are marked on the page. The prompt text updates as you type.
- Which AI tools is this prompt written for?
- It works best with ChatGPT, Claude, Gemini, Cursor. Copy the filled prompt and paste it into the tool you already use.
- What should a good result look like?
- 1. Query: ```sql -- Monthly paid revenue for 2025, excluding soft-deleted orders SELECT date_trunc('month', created_at) AS month, SUM(total_cents) AS revenue_cents FROM orders WHERE deleted_at IS NULL AND status = 'paid' AND created_at >= '2025-01-01' AND created_at < '2026-01-01' GROUP BY 1 ORDER BY 1; ``` 2. Filters to paid, non-deleted rows in 2025, truncates to month, sums totals. 3. Edge cases: NULL deleted_at means active; duplicate months won't appear; timestamptz uses …
Best for
- Developers
- Data analysts
- Engineers
Related Prompts
More prompts like this — same topic and style.