Home / SQL / Practice problems / Medium

Total spent per customer

Mediumrow order matters

Show each customer's name and spent (total of their delivered orders), for customers who spent anything, highest first.

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
namespent
Grace Lee2517.0
Ava Wilson1769.0
Sita Gurung1501.5
Liam Walker1417.0
Isla Taylor1205.0
Chloe Davies1105.5
Zoe Clarke1048.0
Mia Chen826.0
Olivia Smith808.0
Jack Murphy570.0
Aarav Sharma427.0
Lucas Martin326.0
Leo Rossi273.0
Ethan Nguyen172.5
Priya Patel135.0
Hiroshi Tanaka67.0

16 rows

Show a solution
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 WHERE o.status = 'delivered' GROUP BY c.customer_id ORDER BY spent DESC;

SQL used

New to SQL? The free SQL course teaches everything these problems use, with a self-checking exercise in every lesson.