SQL: revenue without cancelled orders (status history)
The question: Total revenue from orders that were never cancelled. Each order gets a new row in order_status every time its status changes.
orders
| order_id | amount |
|---|---|
| 1 | 500 |
| 2 | 300 |
| 3 | 200 |
order_status
| order_id | status |
|---|---|
| 1 | placed |
| 1 | delivered |
| 2 | placed |
| 2 | cancelled |
| 3 | placed |
This query looks right. Run it, decide if it is, and fix it.
SELECT SUM(o.amount) AS revenue FROM orders o JOIN order_status s ON s.order_id = o.order_id WHERE s.status <> 'cancelled';
Show the answer and why the first query is wrong
Why the usual answer is wrong
SELECT SUM(o.amount) AS revenue FROM orders o JOIN order_status s ON s.order_id = o.order_id WHERE s.status <> 'cancelled';
Order 2 was cancelled, but its 'placed' row passes the filter, so it's still counted. Order 1 has two rows that pass, so its 500 is added twice.
The answer
SELECT SUM(o.amount) AS revenue FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM order_status s WHERE s.order_id = o.order_id AND s.status = 'cancelled' );
The question is about orders, so start from orders (one row each) and drop any order that has a cancelled row anywhere in its history.
When a table has many rows per thing, filtering rows answers a question about rows, not about the thing. Ask: does one row here mean one order?
Practise more like this, on real business data
324 interview-style questions on 35 tables with the duplicates, NULLs and missing weeks interviewers test. Free.