Intermediate
Case 25 of 50
Case 025: The Tie Breaker
customersorders
A customer complains their rank on the loyalty leaderboard "jumped weirdly."
The manager asks Alex to investigate: "Could you show me the ranking three different ways, so I can see what's actually going on with the ties?"
The data Alex is looking at
customers
| id | name | country | signup_date |
|---|---|---|---|
| 1 | Sam | Germany | 2026-01-02 |
| 2 | Nina | Canada | 2026-01-05 |
| 3 | David | Germany | 2026-01-10 |
| 4 | Maya | Japan | 2026-01-15 |
| 5 | Daniel | Canada | 2026-01-20 |
| … 5 more rows | |||
orders
| id | customer_id | order_date |
|---|---|---|
| 101 | 1 | 2026-01-05 |
| 102 | 2 | 2026-01-08 |
| 103 | 3 | 2026-01-12 |
| 104 | 1 | 2026-01-20 |
| 105 | 4 | 2026-02-01 |
| … 23 more rows | ||
TASK
Help Alex answer the manager:
“Rank every customer by number of orders placed (most orders first), showing their order count and the rank three ways: RANK, DENSE_RANK, and ROW_NUMBER.”
Write a query above and hit Run to see what comes back.
No hints requested yet — click "Get a hint" when you're stuck.
Alex's final query — this is for reference. Copy it into your own thinking, not into the editor above.
SELECT
name,
order_count,
RANK() OVER (ORDER BY order_count DESC) AS rank_with_gaps,
DENSE_RANK() OVER (ORDER BY order_count DESC) AS rank_no_gaps,
ROW_NUMBER() OVER (ORDER BY order_count DESC) AS row_num
FROM (
SELECT
customers.name,
COUNT(orders.id) AS order_count
FROM customers
JOIN orders ON orders.customer_id = customers.id
GROUP BY customers.name
) AS order_counts
ORDER BY order_count DESC;
What that query returns
Case Debrief
RANK vs DENSE_RANK vs ROW_NUMBER
- RANK skips numbers after a tie; DENSE_RANK never skips; ROW_NUMBER ignores ties entirely and hands out a unique sequence regardless.
- Quick tip: notice the (SELECT ... GROUP BY customers.name) AS order_counts in the FROM clause — wrapping a query in parentheses and giving it a name turns it into a subquery that the outer query can select from, filter, or rank over just like a real table.