Intermediate#07 · Multi-table JOIN, SUM

Revenue by category

Return each category name and its total revenue, highest first. Revenue is quantity × unit_price from order_items (use the price actually charged, not the list price). Leave out cancelled orders and round to 2 decimals.

Write your query in the lab. It runs in your browser and tells you exactly what's wrong if it doesn't match.

Solve it in the lab

Hints

Hint 1

Join four tables: categories → products → order_items → orders.

Hint 2

Filter with o.status <> 'cancelled', then ROUND(SUM(oi.quantity * oi.unit_price), 2).

Expected output

Your result should match this, in this order (6 rows).

name	revenue
Laptops	32322.2
Peripherals	8482.8
Monitors	7507.3
Networking	4959.9
Storage	4673.7
Accessories	4668.4

Model answer

Show the solution

SELECT c.name, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM categories c
JOIN products p     ON p.category_id = c.category_id
JOIN order_items oi ON oi.product_id = p.product_id
JOIN orders o       ON o.order_id = oi.order_id
WHERE o.status <> 'cancelled'
GROUP BY c.name
ORDER BY revenue DESC;

Other queries that return the same rows are correct too. The lab checks your result, not your exact wording.

Next challenge: Top five spenders →