Coding
Design a database schema from requirements
Turn feature requirements into a normalized schema with keys, constraints, indexes and the tradeoffs explained.
No ratings yet
4fill-in fields0copies
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.
Prompt template
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.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.
Why it works
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.
Licensed CC0 (public domain) Version 1 · history
Comments (0)
Sign in to join the discussion.
No comments yet. Be the first to share how it worked.