Stretch#11 · HAVING
Loyal customers
Return the email and number of orders (order_count) for customers who have placed more than 5 orders of any status. Most orders first, then by email.
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 labHints
Hint 1
WHERE filters rows before grouping. HAVING filters groups after.
Hint 2
HAVING COUNT(*) > 5.
Expected output
Your result should match this, in this order (9 rows).
| order_count | |
|---|---|
| chloe.martin@example.com | 13 |
| liam.murphy@example.com | 11 |
| thandi.ndlovu@example.com | 10 |
| wes.hughes@example.com | 8 |
| amara.okafor@example.com | 7 |
| zoe.walsh@example.com | 7 |
| diego.alves@example.com | 6 |
| esther.mensah@example.com | 6 |
| hugo.moreau@example.com | 6 |
Model answer
Show the solution
SELECT c.email, COUNT(*) AS order_count FROM customers c JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id HAVING COUNT(*) > 5 ORDER BY order_count DESC, c.email;
Other queries that return the same rows are correct too. The lab checks your result, not your exact wording.