AI Hints
Homeโ€บSQL Tutorialโ€บPractice Gym: Groups and Joins
SQL ยท Lesson 119 of 126

Gym: Join Workout

Three join challenges.

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

Now joins. Remember to follow the arrows. Customers to orders, orders to order items, order items to books.

Gym: Join Workout
From the video: Gym: Join Workout
  • Follow the arrows
  • customers, orders
  • order_items, books

Busiest cities

Which three cities placed the most orders? Pause and try. Here's a solution. Join customers to orders, skip missing cities, group by city, and count. Dublin is the busiest, with one hundred and sixty five orders.

โœ๏ธ Exercise: Busiest cities

Top 3 cities by number of orders

Show hint

JOIN orders, GROUP BY c.city, COUNT(o.order_id)

Show solution
solution.sqlsql
SELECT c.city, COUNT(o.order_id) AS orders
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE c.city IS NOT NULL
GROUP BY c.city
ORDER BY orders DESC
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  city   โ”‚ orders โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Dublin  โ”‚ 165    โ”‚
โ”‚ Sydney  โ”‚ 145    โ”‚
โ”‚ Toronto โ”‚ 137    โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Best-selling authors

Which three authors have sold the most copies? Pause and try. Here's a solution. Join books to order items, group by author, and sum the quantity. Mira Hale has sold four hundred and seventy one copies.

โœ๏ธ Exercise: Best-selling authors

Top 3 authors by copies sold

Show hint

JOIN order_items, GROUP BY b.author, SUM(oi.quantity)

Show solution
solution.sqlsql
SELECT b.author, SUM(oi.quantity) AS copies_sold
FROM books AS b
JOIN order_items AS oi ON oi.book_id = b.book_id
GROUP BY b.author
ORDER BY copies_sold DESC
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  author   โ”‚ copies_sold โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Mira Hale โ”‚ 471         โ”‚
โ”‚ Kai Lund  โ”‚ 445         โ”‚
โ”‚ Tom Avery โ”‚ 406         โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Repeat returners

Which customers have returned two or more orders? Pause and try. Here's a solution. Join, filter on returned, group by customer, and having count at least two. Five customers. Helen might check if something keeps going wrong with their deliveries.

โœ๏ธ Exercise: Repeat returners

Customers with 2 or more returned orders

Show hint

WHERE status = 'returned' GROUP BY customer HAVING COUNT(*) >= 2

Show solution
solution.sqlsql
SELECT c.first_name, c.last_name, COUNT(*) AS returns
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'returned'
GROUP BY c.customer_id
HAVING COUNT(*) >= 2;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ first_name โ”‚ last_name โ”‚ returns โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Yara       โ”‚ Rossi     โ”‚ 2       โ”‚
โ”‚ Chloe      โ”‚ Garcia    โ”‚ 2       โ”‚
โ”‚ Carmen     โ”‚ Costa     โ”‚ 2       โ”‚
โ”‚ Emma       โ”‚ Khan      โ”‚ 2       โ”‚
โ”‚ Zane       โ”‚ Tanaka    โ”‚ 2       โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

When a join looks wrong

If a join gives a strange answer, here's what to check. Is every table joined on the right pair of IDs? Did you count rows, when you meant distinct orders? Should it be a left join, to keep the zeros? And did a where on the right table quietly turn your left join back into an inner join?

Right IDs?check every ON
Double counting?COUNT(DISTINCT ...)
Need zeros?LEFT JOIN
WHERE on the right tablecan undo a LEFT JOIN

Summary

Join, filter, group, count. That rhythm answers most business questions. One last join challenge, with all four tables.

Key takeaways
  • JOIN then GROUP BY
  • COUNT or SUM
  • HAVING for groups
Next up: Gym: Four Tables โ€” the big one