Intermediate
Case 21 of 50
Case 021: The Leaderboard
customersorderspayments
Welcome to Intermediate. The fundamentals are locked in. Now it's time to think more like an analyst.
Your first challenge: window functions. The manager has a question for the upcoming sales meeting:
"Could you rank every customer by how much they've spent? I want to see who's #1."
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 | ||
payments
| id | order_id | amount |
|---|---|---|
| 601 | 101 | 6200 |
| 602 | 102 | 3500 |
| 603 | 103 | 3600 |
| 604 | 104 | 60000 |
| 605 | 105 | 12000 |
| … 23 more rows | ||
TASK
Help Alex answer the manager:
“Rank every customer by total spending, highest first, showing both their total spent and their rank.”
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
customers.name,
SUM(payments.amount) AS total_spent,
RANK() OVER (ORDER BY SUM(payments.amount) DESC) AS spending_rank
FROM customers
JOIN orders ON customers.id = orders.customer_id
JOIN payments ON payments.order_id = orders.id
GROUP BY customers.name;
What that query returns
Case Debrief
RANK() window function
- RANK() OVER (ORDER BY ...) assigns a rank to every row based on a value.
- DESC inside that ORDER BY is what makes rank #1 the biggest spender — RANK() OVER (ORDER BY SUM(payments.amount) DESC) ranks highest-to-lowest. Drop the DESC (or write ASC) and rank #1 would go to the smallest spender instead.
- It can rank an aggregate like SUM() in the very same query that computes it — no extra pass needed.
- Quick tip: this query spells out customers, orders, and payments in full every time. Once you're joining several tables, aliasing them — customers AS c JOIN orders AS o JOIN payments AS p — lets you write c.name, o.customer_id, p.amount instead, which gets easier to read fast as queries grow.