AI Hints
Homeโ€บSQL Tutorialโ€บJoining Many Tables
SQL ยท Lesson 50 of 126

Practice: Revenue by Country

Four tables, one answer.

โฑ 2 min readโ–ถ Video at 1:10:52๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Practice: Revenue by Country
From the video: Practice: Revenue by Country
  • 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.

โœ๏ธ Exercise: Revenue by country

Revenue per country, delivered + shipped only, biggest first

Show hint

customers โ†’ orders โ†’ order_items โ†’ books, GROUP BY c.country

Show solution
solution.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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
Next up: Self Joins โ€” a table that joins itself