Home / SQL / Practice problems / Hard

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
Show the expected result
namespentspend_rank
Grace Lee3748.51
Zoe Clarke3534.52
Ava Wilson2756.03
Sita Gurung2017.54
Hiroshi Tanaka1817.55
Liam Walker1417.06
Isla Taylor1280.07
Aarav Sharma1242.08
Bikash Thapa1149.09
Chloe Davies1129.510
Mia Chen826.011
Olivia Smith808.012
Jack Murphy764.013
Lucas Martin386.014
Leo Rossi273.015
Ethan Nguyen172.516
Priya Patel135.017

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.