I need a SQL query for the analysis below. Work only from the schema and business rules I provide; do not guess column meanings, table relationships, database dialect features, or missing data. DATABASE DIALECT: [DATABASE DIALECT] SCHEMA: [TABLE SCHEMA] ANALYSIS REQUEST: [ANALYSIS REQUEST] BUSINESS RULES: [BUSINESS RULES] SAMPLE DATA OR EXPECTED RESULT: [SAMPLE DATA OR EXPECTED RESULT] First, identify the grain of the requested output: one row per what? State any assumptions you had to make. Then write one runnable query, using CTEs with meaningful names where they make the logic easier to audit. Fully qualify ambiguous columns, use explicit JOIN types, and put date boundaries and timezone assumptions in the query or notes.
Handle the details analysts commonly miss: duplicate records, one-to-many joins that inflate totals, NULL values, inclusive versus exclusive date ranges, integer division, and filters that belong in JOIN conditions rather than WHERE clauses. For ratios, use NULLIF to prevent division-by-zero errors. If I request a ranking, specify how ties are handled. If I request a cohort, retention, or running total, make the cohort date and window definition explicit. Produce this exact output: 1. A brief assumptions list. 2. The SQL in one code block. 3. A plain-English explanation of each CTE or major clause. 4. Three validation queries or checks, including a row-count or duplication check. 5. Any question that must be answered before the query can be trusted. Before answering, self-check that every referenced table and column exists in my schema and that aggregations cannot be inflated by a join. Ask up to 3 clarifying questions only if a required input is missing; otherwise provide the query.
Fill in
| Placeholder | What to enter | Example |
|---|---|---|
| [DATABASE DIALECT] | Name the SQL system, such as PostgreSQL, BigQuery, Snowflake, MySQL, or SQL Server. | PostgreSQL 15 |
| [TABLE SCHEMA] | Paste the relevant tables, columns, data types, keys, and known relationships. | orders(id PK, customer_id, created_at timestamptz, status, total_cents); order_items(id PK, order_id FK, sku, quantity, unit_price_cents); customers(id PK, plan) |
| [ANALYSIS REQUEST] | Describe the metric, filters, grouping, time period, and desired output. | Monthly paid revenue and unique paying customers by plan for January through June 2026. |
| [BUSINESS RULES] | List definitions such as which statuses count, timezone, cancellation treatment, and exclusions. | Count only orders with status='paid'; revenue is sum of total_cents; report in America/New_York; exclude internal plan. |
| [SAMPLE DATA OR EXPECTED RESULT] | Paste a few representative rows or describe the result you expect. | A table with month, plan, paid_revenue_dollars, and paying_customers. |
How to use
- Paste the exact dialect and only the tables needed for the question.
- State the output grain and business definitions before pasting the prompt.
- Run the validation checks against a small date range before using the full result.
- Follow up with: “Rewrite this for performance at our table sizes and explain the indexes or partitions that would help.”
Variations
Debug a query
Use this when a query errors or returns a suspicious total.
I need you to debug this [DATABASE DIALECT] query. SCHEMA: [TABLE SCHEMA] QUERY: [QUERY] ERROR OR UNEXPECTED RESULT: [ERROR OR RESULT] EXPECTED LOGIC: [EXPECTED LOGIC] Identify the root cause before rewriting it. Return: (1) the likely cause, (2) a corrected query, (3) a line-by-line explanation of what changed, and (4) two checks that distinguish the old result from the corrected one. Do not invent schema details. Check joins, aggregation grain, NULL behavior, date filters, and dialect syntax. Ask up to 3 questions only if required information is missing.
Explain SQL
Use this when you inherited a query and need to verify what it actually measures.
Explain and audit this SQL query for a non-technical stakeholder. DATABASE DIALECT: [DATABASE DIALECT] SCHEMA: [TABLE SCHEMA] QUERY: [QUERY] BUSINESS CONTEXT: [BUSINESS CONTEXT] Return a short plain-English summary, then a table listing each CTE or major clause, its purpose, output grain, and risk of misinterpretation. Flag unsupported assumptions, duplicate-risk joins, hidden filters, NULL behavior, and any mismatch between the query and business context. Finish with three questions the data owner should answer. Do not rewrite the query unless a clear defect exists.
Build a data model
Use this when you need a reliable reporting query from several source tables.
Design a SQL reporting approach for this metric. DATABASE DIALECT: [DATABASE DIALECT] SOURCE TABLES: [SOURCE TABLES] METRIC DEFINITION: [METRIC DEFINITION] REFRESH NEEDS: [REFRESH NEEDS] Propose the output grain, recommended fact and dimension tables or CTE layers, and one production-ready query skeleton. Include keys, deduplication rules, late-arriving-data treatment, and tests for uniqueness and reconciliation. Do not assume fields that are not listed. Explain which transformations belong in a reusable model rather than an ad hoc dashboard query, and ask up to 3 questions only where a decision changes the metric.
Tips
- Write the requested output grain first, such as “one row per customer per calendar month”; it prevents accidental double counting.
- Include the database dialect because date functions, safe casting, QUALIFY support, and identifier rules vary substantially.
- When joining an order table to order items, aggregate items to order level before joining if the metric is order revenue.
- Treat a query result as untrusted until you reconcile a small period to a known source total and inspect duplicate counts.
FAQ
Can AI write SQL without my schema?
It can draft a pattern, but it cannot produce a trustworthy runnable query without real table and column names. Paste the relevant schema and business definitions.
Why did the generated query return too much revenue?
The usual cause is a one-to-many join, such as orders joined directly to order items before summing order-level revenue. Check the output grain and aggregate the many-side table first.
Should I ask for optimized SQL at the start?
Get correct logic first. Then provide table size, partitions, indexes, and an execution plan if you need performance work.