Help me write a production-safe SQL query for this request. SQL DIALECT: [SQL DIALECT] SCHEMA: [SCHEMA] BUSINESS QUESTION: [BUSINESS QUESTION] RULES AND DEFINITIONS: [RULES AND DEFINITIONS] SAMPLE DATA OR EXPECTED RESULT: [SAMPLE DATA OR EXPECTED RESULT] First, identify the grain of the requested result: one row per what? State any assumption about keys, dates, time zones, status values, and duplicate records. If a required input is missing, ask up to 3 clarifying questions before writing SQL.
Then produce: 1. A single runnable SQL query, using named CTEs for distinct stages when that improves auditability. 2. A brief explanation of each CTE or major clause in plain English. 3. A result-grain statement and a list of output columns with their meaning. 4. Three targeted validation queries or checks. Use explicit column names, qualified aliases, and ANSI-style joins unless this dialect requires another form. Do not use SELECT *. Do not use an INNER JOIN where unmatched records may need to remain; explain the join choice. Aggregate only after controlling join fanout. For date filters, use inclusive/exclusive boundaries rather than BETWEEN when timestamps are involved. Put filters on an outer-joined table in the JOIN condition when needed to preserve null rows. Use NULLIF for possible zero denominators and explain how nulls are handled. Before answering, self-check that every selected non-aggregate column is grouped appropriately, every join has a stated key, and no metric can be duplicated by a one-to-many join. Flag any part of the request that the schema does not support. Do not invent tables, columns, keys, or sample results.
Fill in
| Placeholder | What to enter | Example |
|---|---|---|
| [SQL DIALECT] | Enter the database and version if relevant, such as PostgreSQL 16, BigQuery, or SQL Server. | BigQuery Standard SQL |
| [SCHEMA] | Paste relevant table names, columns, primary keys, and known relationships. | orders(order_id, customer_id, created_at, status, total_cents); customers(customer_id, signup_at, country); order_items(order_id, sku, quantity) |
| [BUSINESS QUESTION] | Describe the metric, report, or data question you need answered. | Show monthly paid-order revenue and unique purchasers by customer country for Q2 2026. |
| [RULES AND DEFINITIONS] | List business rules such as active-customer definitions, exclusions, time zone, and date range. | Paid means status = 'paid'; report dates use America/New_York; exclude refunded orders; revenue is total_cents / 100. |
| [SAMPLE DATA OR EXPECTED RESULT] | Paste a few representative rows or describe the columns and values the result should contain. | One row per calendar month and country, with month_start, country, revenue_usd, and unique_purchasers. |
How to use
- Paste only the tables and columns the query needs, including how tables join.
- State the output grain before submitting, such as one row per customer per month.
- Check the proposed join keys and validation queries against a small known slice of data.
- Follow up with: "Rewrite this for performance on [DATABASE] and explain the indexes or partitions it would use."
Variations
Debug a query
Use this when a query errors or returns a suspicious count.
Diagnose this SQL query without rewriting its business intent. SQL DIALECT: [SQL DIALECT] QUERY: [QUERY] ERROR OR UNEXPECTED RESULT: [ERROR OR UNEXPECTED RESULT] SCHEMA NOTES: [SCHEMA NOTES] Return the likely cause, the smallest corrected query, and a line-by-line explanation of what changed. Check aliases, aggregation rules, null behavior, date logic, and join cardinality. Provide two diagnostic queries that prove the fix. Do not assume column types or relationships not shown. Ask up to 3 questions only if the missing detail prevents diagnosis.
Optimize a query
Use this when a correct query is slow or expensive.
Review this SQL query for performance while preserving its exact result set. DATABASE: [DATABASE] QUERY: [QUERY] TABLE SIZES AND PARTITIONING: [TABLE SIZES AND PARTITIONING] EXECUTION DETAILS: [EXECUTION DETAILS] Return an optimized version, then list each change with its likely effect on scans, joins, sorting, and memory. Preserve null semantics and output grain. Recommend concrete indexes, partitions, clustering, or predicate changes only when supported by the details provided. Include an equivalence check and a before-versus-after measurement plan. Ask up to 3 questions if a key execution detail is missing.
Explain a query
Use this when you inherited SQL and need to audit its logic.
Explain and audit this SQL for an analyst who knows the business but not the query. SQL DIALECT: [SQL DIALECT] QUERY: [QUERY] BUSINESS CONTEXT: [BUSINESS CONTEXT] KNOWN TABLES: [KNOWN TABLES] Write a plain-English walkthrough in execution order, identify the result grain, and map each output field to its source and transformation. Then flag risks involving duplicate rows, dropped records, nulls, time boundaries, and implicit assumptions. Finish with three questions the query owner should answer before this is used for reporting. Do not claim that a metric is correct unless the shown schema supports it.
Tips
- Write the expected result grain in one sentence before asking for SQL; it exposes many duplicate-count errors before the query exists.
- Give the AI actual join keys and cardinality notes, especially where an order can have many items or events.
- For timestamped data, specify both the reporting time zone and whether the end date includes the full final day.
- Run validation checks on a narrow date range and compare totals with a trusted dashboard or manually counted records.
FAQ
Can Copilot write SQL from a plain-English question?
Yes, but it needs the relevant schema and business definitions. A natural-language request alone cannot establish the correct join keys or meaning of a status field.
How do I stop AI-generated SQL from double counting?
State the desired row grain and disclose one-to-many tables. Ask for join-cardinality checks before accepting any aggregate.
Should I paste confidential data into the prompt?
Avoid raw customer data unless your approved AI environment permits it. Column names, masked examples, and aggregate samples are usually enough.