Write a SQL query from plain English

Describe the question and your tables, and get a correct, readable query with an explanation and edge cases.

Change note: Initial version

Prompt

Write a {{dialect}} query that answers this question:

{{question}}

Schema (tables, columns, keys, anything relevant):
{{schema}}

Rules:
- Use only tables and columns that appear in the schema. If something I asked for is not in the schema, say so and ask rather than inventing a column.
- Prefer CTEs for multi-step logic, explicit JOIN syntax, and clear aliases. Format for readability.
- Handle NULLs, duplicates and ties deliberately, and tell me how.
- Mention any assumptions about the data (for example, whether a status value means active).

Output:
1. The query in a single code block.
2. A short plain-language explanation of how it works, step by step.
3. Edge cases and gotchas: what could make the result wrong or surprising.
4. Which indexes would help if this table is large.
5. A tiny sample of input rows and the output the query should return, so I can verify it.

Variables

• {{dialect}}SQL dialectselect

  Options: PostgreSQL, MySQL, SQL Server, SQLite, BigQuery, Snowflake, Oracle

  Default: PostgreSQL

• {{question}}What do you want to know?long

  Default: For each customer, show their total spend in the last 90 days and the date of their most recent order. Only include customers with at least 3 orders.

• {{schema}}Schemalong

  Default: customers(id, name, email, created_at) orders(id, customer_id, total_cents, status, created_at)

  Paste CREATE TABLE statements or a short list.

Example output

```sql
WITH recent AS (
  SELECT customer_id,
         SUM(total_cents) / 100.0 AS spend_90d,
         MAX(created_at)::date AS last_order_date,
         COUNT(*) AS order_count
  FROM orders
  WHERE status = 'paid'
    AND created_at >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY customer_id
)
SELECT c.id, c.name, r.spend_90d, r.last_order_date
FROM recent r
JOIN customers c ON c.id = r.customer_id
WHERE r.order_count >= 3
ORDER BY r.spend_90d DESC;
```

**How it works:** the CTE aggregates paid orders from the last 90 days per customer, then joins names and keeps those with 3 or more orders.

**Gotchas**
- I assumed `status = 'paid'` means a real purchase; refunds are not in the schema.
- "At least 3 orders" is counted inside the 90-day window. If you meant lifetime orders, move the count to a separate CTE.

**Indexes:** `orders (status, created_at, customer_id)`.

**Check:** a customer with orders of 20.00, 30.00 and 50.00 should return 100.00.

Notes

Telling the model to refuse invented columns and to state its assumptions prevents the most common SQL failure, plausible but wrong queries. The sample-rows check gives you a fast way to verify.