Gym: Join Workout
Three join challenges.
Now joins. Remember to follow the arrows. Customers to orders, orders to order items, order items to books.

- 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.
Top 3 cities by number of orders
Show hint
JOIN orders, GROUP BY c.city, COUNT(o.order_id)
Show solution
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;โโโโโโโโโโโฌโโโโโโโโโ โ 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.
Top 3 authors by copies sold
Show hint
JOIN order_items, GROUP BY b.author, SUM(oi.quantity)
Show solution
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;โโโโโโโโโโโโโฌโโโโโโโโโโโโโโ โ 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.
Customers with 2 or more returned orders
Show hint
WHERE status = 'returned' GROUP BY customer HAVING COUNT(*) >= 2
Show solution
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;โโโโโโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโโ โ 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?
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