Top N
Finance pings Harry during the quarterly review: “Just give me the top 5. Our best customers, by how much they’ve spent with us.”
Easy, this is just ORDER BY and LIMIT again — you told me that pattern would come back.
It did come back. But look at what you're sorting this time. 'How much a customer has spent' isn't a column sitting in any table.
Back in “Can we sort this?”, top N meant sorting a column that already existed — signup_date was right there on the row. This time, “total spent” has to be built first: it’s a sum across every order a customer placed, in a completely different table. The pattern doesn’t care where the number comes from, though. Once you have a value per row — raw column or computed — top N is always the same two steps: sort by it, then cut it off.
Compute the value, then sort and cut.
First, get one row per customer with their total spend — a join back to orders, grouped and summed:
SELECT c.name, SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total_spent DESC
LIMIT 5;| name | total_spent |
|---|---|
| George Lee | 259.97 |
| Alice Sharma | 257.97 |
| Emma Wilson | 250.46 |
| Bob Turner | 229.98 |
| Julia Souza | 177.99 |
Same two moves as always: ORDER BY on the value, LIMIT to cut it off. The only new part was building the value in the first place.
Finance, looking at it: “Wait — George Lee is on top, but one of his two orders is still pending. And Alice has a cancelled order in there. Should those count as ‘spent’?”
Good catch. Money that was cancelled or refunded never actually landed. Add a filter before the grouping happens:
SELECT c.name, SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY c.id, c.name
ORDER BY total_spent DESC
LIMIT 5;| name | total_spent |
|---|---|
| Bob Turner | 229.98 |
| Emma Wilson | 223.46 |
| Alice Sharma | 218.97 |
| George Lee | 199.99 |
| Julia Souza | 177.99 |
The list actually reshuffles — George drops from #1 to #4, Bob jumps from #4 to #1. Same pattern, but the leaderboard was wrong until the numbers going into it were honest. That’s worth remembering: top N is only as trustworthy as the value you’re ranking.
Running…
Try it: What if you flip ORDER BY total_spent DESC to ASC, keeping LIMIT 5?
Run your query to see results here.
Same query, opposite direction — now it’s bottom N: the five customers who’ve spent the least (among those who’ve ordered at all). Top N and bottom N are the exact same pattern; only the sort direction changes. Notice Chloe and Hana don’t show up in either list — the JOIN only keeps customers who have orders to sum in the first place. We’ll deal with that gap properly later in this chapter.
The catalog team wants NOVA’s 3 best-selling products by revenue — completed orders only — for the homepage banner. Return each product’s name and its total revenue.
Hint 1
This is the same shape as the customer example: compute a value per row first, then sort and cut. Here, the 'row' is a product and the value is total revenue.
Hint 2
Join products to orders on the product id, filter to completed orders, then GROUP BY the product before summing.
Hint 3
SELECT p.name, SUM(o.amount) AS total_revenue FROM products p JOIN orders o ON o.product_id = p.id WHERE o.status = 'completed' GROUP BY p.id, p.name ORDER BY total_revenue DESC LIMIT 3;
Solution
Running…
The Pulse Smartwatch comes out on top by a wide margin — two completed orders at $199.99 each will do that. Nothing about solving this challenge required anything you didn’t already know: a join, a filter, a group, an order, a limit. That’s the whole point of naming the pattern — once you recognize “give me the top N of something,” you already know the shape of the answer before you write a single line.
Top N is always “sort, then cut” — ORDER BY plus LIMIT. The value you’re ranking can be a raw column or something you compute with a JOIN and GROUP BY first; the pattern doesn’t change, and neither does its correctness depend on you filtering out rows that shouldn’t count before you rank them.