Why does the SQL query report £800 instead of £300?
An AI draft joins order totals to line items, then sums order value.
The question
Harbor Market needs completed-order revenue. Each order total already includes all its lines.
| Order total (£) | |
|---|---|
| O-1 | 100 |
| O-2 | 200 |
Authored data. Both orders are completed; no refunds.
| Order ID | Line value (£) | |
|---|---|---|
| L-1 | O-1 | 60 |
| L-2 | O-1 | 40 |
| L-3 | O-2 | 80 |
| L-4 | O-2 | 70 |
| L-5 | O-2 | 50 |
Line values reconcile to each order total.
SELECT SUM(o.total_gbp) FROM orders o JOIN order_lines l ON o.order_id = l.order_id WHERE o.status = 'completed';
“Explain the result, repair the grain, and give a small test.”
Not saved by this site. Your browser may restore text when you navigate back.
A strong answer, step by step
Open each step in turn. This is one strong answer, not the only one.
Step 1: Trace the five rows
What would you do here? Open the move you’d make.
List each joined order total
The strong move. One-to-many joins repeat order-level measures.
Use the total from the joined export
Tempting, but it misses something. A valid foreign-key join can still multiply an order-level measure.
See how a strong candidate says it
“O-1 appears twice and contributes £200. O-2 appears three times and contributes £600. The joined sum is £800 although the two orders total £300.”
Why it works: One-to-many joins repeat order-level measures.
Step 2: Choose the measure grain
What would you do here? Open the move you’d make.
Use SUM(DISTINCT total_gbp)
Tempting, but it misses something. Two legitimate orders can have the same value; DISTINCT would discard one.
Sum at one row per order
The strong move. The repair matches the business measure.
See how a strong candidate says it
“For total completed-order revenue, sum the completed orders table at one row per order. If line detail is required, aggregate it to one row per order before joining.”
Why it works: The repair matches the business measure.
Step 3: Test a counterexample
What would you do here? Open the move you’d make.
Test two different orders with the same total
The strong move. Equal-value orders expose a false repair.
Check that totals match on these two orders
Tempting, but it misses something. The original packet does not expose the DISTINCT bug.
See how a strong candidate says it
“Add another completed £100 order. Expected order revenue becomes £400. A SUM(DISTINCT total_gbp) repair still returns £300 and fails. Also check orphan lines and missing orders.”
Why it works: Equal-value orders expose a false repair.
Step 4: Explain the impact
What would you do here? Open the move you’d make.
Update the chart with the repaired total
Tempting, but it misses something. Stakeholders may already have made a decision from the wrong total.
Report the defect and validate scope
The strong move. A narrow repair still needs an explicit revenue contract.
See how a strong candidate says it
“The original report overstates revenue by £500. I would correct the query and downstream report, retain the failing fixture, and confirm whether taxes, refunds and status timing belong in the production definition.”
Why it works: A narrow repair still needs an explicit revenue contract.
The answer, in short
- Decision
- Sum completed orders at order grain.
- Because
- Line joins multiply the existing order measure.
- What would change it
- A question about line-category revenue would require line amounts and a different measure.
Common traps
- Repairing duplication with DISTINCT on money.
- Assuming SQL that runs is SQL that answers the request.
Check your answer
- Did you trace £200 + £600 = £800?
- Did you preserve the true £300 order total?
- Did your repair retain two equal-value orders?
- Did you separate order revenue from line-category revenue?
Last reviewed Nov 10, 2026