# Illustrative revenue responses

These are hand-authored teaching examples of plausible incorrect suggestions.
They are **not actual model transcripts**, model benchmark results, or evidence
that any named model was run. The exercise runs the SQL locally, not an LLM.

## Initial suggestion — joined header total

> Join orders to their items, keep paid orders, and sum the order amounts.

```sql
SELECT SUM(o.amount_cents) AS revenue_cents
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'paid';
```

Review: each header appears once per line after the join. A $100 order with two
lines becomes $200. The single-order fixture returns 20000 cents; expected 10000.

## Superficial repair — distinct amount

> Use DISTINCT inside SUM so duplicated totals are counted once.

```sql
SELECT SUM(DISTINCT o.amount_cents) AS revenue_cents
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'paid';
```

Review: this passes the first fixture, then returns 10000 instead of 20000 when
two different paid orders both total $100. Equal amounts do not mean equal orders.

A defensible repair must choose a grain: sum each paid order header once, or sum
quantity times the sale-time price of each paid order line. `products.unit_price_cents`
is the current catalog price and can differ from the captured sale price. The
fixtures use quantity two on a $70 line so forgetting quantity also fails.

To replay either suggestion, save only its SQL block in `candidate.sql`, then run:

```sh
python3 revenue-grain.py --candidate-file candidate.sql
```

A rejected query exits 1. Inspect the business assumptions before trusting even
a query that passes both small fixtures: status policy, refunds, tax, shipping,
multiple currencies and line/header reconciliation remain separate decisions.