Lesson 37 / 37 · Debugging

Capstone: the revenue is too high

A real bug. The join to order_items multiplies each order by its number of items, and the SUM counts the order amount once per item. dequery flags it at the AGGREGATE step.

The query

SELECT c.name, sum(o.amount) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'paid'
GROUP BY c.name
ORDER BY revenue DESC

Fix it: remove the order_items join, which the query does not need, and Chen's revenue falls from 442 to 221, his one paid order counted once. When the items are needed, aggregate the orders before joining them.

All lessons