Practice: Revenue by Country
Four tables, one answer.
Helen is thinking about advertising abroad. She wants to know how much revenue comes from each country. The country is in customers. The price is in books. So you'll need all four tables.

- Advertise abroad?
- Revenue per country
- All four tables
Revenue by country
Your turn. Show revenue for each country, biggest first, counting only delivered and shipped orders. Pause the video, and give it a real try. Here's a solution. Customers, to orders, to order items, to books. Then filter, group by country, and sort. The USA and the UK are almost level, at about ten thousand dollars each.
Revenue per country, delivered + shipped only, biggest first
Show hint
customers โ orders โ order_items โ books, GROUP BY c.country
Show solution
SELECT c.country,
ROUND(SUM(oi.quantity * b.price), 2) AS revenue
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN books AS b ON b.book_id = oi.book_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY c.country
ORDER BY revenue DESC;โโโโโโโโโโโโโฌโโโโโโโโโโโ โ country โ revenue โ โโโโโโโโโโโโโผโโโโโโโโโโโค โ USA โ 10501.38 โ โ UK โ 10046.72 โ โ Australia โ 8220.79 โ โ Canada โ 7233.23 โ โ Ireland โ 4397.45 โ โ Germany โ 3899.17 โ โโโโโโโโโโโโโดโโโโโโโโโโโ
Best customers
One more, and it's very useful. Who are Helen's best customers, by money spent? Same four joins, grouped by customer. Run it. Yara Ali has spent four hundred and forty five dollars. Helen will send her a thank you card.
SELECT c.first_name,
c.last_name,
ROUND(SUM(oi.quantity * b.price), 2) AS spent
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN books AS b ON b.book_id = oi.book_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY c.customer_id
ORDER BY spent DESC
LIMIT 5;โโโโโโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโ โ first_name โ last_name โ spent โ โโโโโโโโโโโโโโผโโโโโโโโโโโโผโโโโโโโโโค โ Yara โ Ali โ 445.27 โ โ Farah โ Novak โ 415.25 โ โ Farah โ Khan โ 385.28 โ โ Olivia โ Chen โ 384.78 โ โ Sam โ Silva โ 362.79 โ โโโโโโโโโโโโโโดโโโโโโโโโโโโดโโโโโโโโโ
Summary
You just answered real business questions, across four tables. That's genuinely useful SQL. Next, a table that joins to itself.
Key takeaways- Four tables, one query
- Revenue per country
- Best customers