What this prompt does
This prompt uses Copilot to write and optimize SQL for your [database], given a schema described by [schema_description]. It generates a query for [primary_query] using proper joins rather than subqueries where joins perform better, plus a reporting query that aggregates [report_metrics] grouped by [dimensions] with correct NULL handling. The point is not just to draft SQL but to reason about why one shape is faster than another.
The structure works because it covers the whole lifecycle from writing through tuning. It takes a [slow_query_pattern] and rewrites it for performance with index suggestions, generates window-function queries for [window_tasks] covering ROW_NUMBER, LAG/LEAD, and running totals, builds a CTE-based query for the hierarchical [recursive_task], and writes data-migration queries that safely perform [migration_task] with transaction safety and rollback. It also explains how to read EXPLAIN ANALYZE output so you can spot bottlenecks yourself, and produces the [orm] equivalent of each query so the raw SQL stays consistent with your application code. Reading EXPLAIN ANALYZE together is how a slow correlated subquery becomes a fast join, and generating the ORM equivalent keeps everything aligned with how the app actually talks to the database.
When to use it
- You need a complex analytical or reporting query written correctly the first time.
- You have a slow query and want it rewritten with index recommendations.
- You need window functions for rankings, month-over-month growth, or running totals.
- You are querying hierarchical data and need a recursive CTE.
- You are writing a data migration and want transaction safety and a rollback path.
- You want the ORM equivalent so raw SQL and application code stay consistent.
Example output
Expect a query toolkit rather than a single statement. You get the primary join-based query, an aggregation and reporting query with NULL handling, a rewritten version of your slow pattern plus suggested indexes, window-function queries, a recursive CTE for the tree, and a transaction-safe migration with rollback. Each often comes with a guide to reading the EXPLAIN ANALYZE plan and an [orm] equivalent, so you can both run the SQL and understand why it performs the way it does before committing it.
Pro tips
- Describe
[schema_description]including rough table sizes, since "millions of rows in orders" changes which plan is actually fast. - Give the real shape of
[slow_query_pattern], such as a correlated subquery in a WHERE clause, so the rewrite targets the genuine bottleneck. - Treat index suggestions as hypotheses and validate them with EXPLAIN ANALYZE on real data before adding them to production.
- Match
[orm]to what you actually use so the generated equivalent fits your code style and naming. - For
[migration_task], always keep the transaction and rollback guidance, because backfills on large tables can lock or fail midway. - Read EXPLAIN ANALYZE together with the query, since the plan, not intuition, tells you whether the rewrite genuinely helped.
