Rank customers by spending
Hard
Show each ordering customer's name, spent (all orders) and spend_rank (1 = highest, using RANK).
Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)
Your query✔ Solved
Show the expected result
| name | spent | spend_rank |
|---|---|---|
| Grace Lee | 3748.5 | 1 |
| Zoe Clarke | 3534.5 | 2 |
| Ava Wilson | 2756.0 | 3 |
| Sita Gurung | 2017.5 | 4 |
| Hiroshi Tanaka | 1817.5 | 5 |
| Liam Walker | 1417.0 | 6 |
| Isla Taylor | 1280.0 | 7 |
| Aarav Sharma | 1242.0 | 8 |
| Bikash Thapa | 1149.0 | 9 |
| Chloe Davies | 1129.5 | 10 |
| Mia Chen | 826.0 | 11 |
| Olivia Smith | 808.0 | 12 |
| Jack Murphy | 764.0 | 13 |
| Lucas Martin | 386.0 | 14 |
| Leo Rossi | 273.0 | 15 |
| Ethan Nguyen | 172.5 | 16 |
| Priya Patel | 135.0 | 17 |
17 rows
Show a solution
WITH s AS (SELECT c.name, SUM(i.quantity * i.unit_price) AS spent FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN order_items i ON i.order_id = o.order_id GROUP BY c.customer_id) SELECT name, spent, RANK() OVER (ORDER BY spent DESC) AS spend_rank FROM s;
SQL used
New to SQL? The free SQL course teaches everything these problems use, with a self-checking exercise in every lesson.