Prompt: Write an SQL Query

You are a database engineer who writes SQL that's easy for the next person to read.

Objective: Write a {{DIALECT — e.g. PostgreSQL, MySQL, SQLite}} query that {{DESCRIBE WHAT THE QUERY SHOULD RETURN}}.

Context: {{TABLE SCHEMAS — paste the columns and types}}

Constraints:
- Use explicit column names, never SELECT *
- Handle NULLs explicitly if the result could contain them
- Add a one-line comment above any non-obvious join or subquery
- Do not invent column or table names that weren't given

Example: "customers who placed more than 3 orders in the last 90 days" is specific enough to write directly; "our best customers" is not.

Output format: Return the query in a single fenced SQL code block, followed by one sentence explaining what it does in plain English.

Before answering, check the query against the schema given and confirm every referenced column actually exists there.

DIALECT — e.g. PostgreSQL, MySQL, SQLite: PostgreSQL
DESCRIBE WHAT THE QUERY SHOULD RETURN: customers who placed more than 3 orders in the last 90 days
TABLE SCHEMAS — paste the columns and types: customers(id int, name text, email text); orders(id int, customer_id int, placed_at timestamp, total numeric)

```sql
-- Customers with more than 3 orders placed in the last 90 days
-- Orders with a NULL placed_at are excluded by the date filter; COALESCE keeps missing names/emails readable
SELECT
    c.id,
    COALESCE(c.name, '') AS name,
    COALESCE(c.email, '') AS email,
    COUNT(o.id) AS order_count

FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.placed_at >= NOW() - INTERVAL '90 days'
GROUP BY c.id, c.name, c.email
HAVING COUNT(o.id) > 3
ORDER BY order_count DESC;
```
This query joins customers to their orders placed in the last 90 days, groups by customer, and keeps only those with more than three orders in that window.

Why the basic version fails

“Write a query to get the top customers” without the schema forces the model to guess table and column names — you get SQL that looks plausible and doesn’t run.

What this prompt fixes

Supplying the schema removes the guessing entirely, and the “confirm every column exists” check catches the model inventing a column name that sounds right but isn’t there. The no-SELECT * rule also means the query still works if the table gains columns later.

Tips per model

• ChatGPT writes clean SQL when given a real schema; without one, it will confidently invent column names — always paste your schema.
• Gemini is good at flagging when a request is ambiguous (e.g. “top customers” by revenue or by order count) — let it ask rather than assuming.
• Paste the exact column types too, not just the names — a date stored as text versus a real timestamp changes how filtering and comparisons need to be written.