Intermediate#08 · JOIN, GROUP BY, LIMIT

Top five spenders

Return the five customers who spent the most, as full_name (first and last name with a space) and total_spent rounded to 2 decimals. Ignore cancelled orders. Highest first.

Write your query in the lab. It runs in your browser and tells you exactly what's wrong if it doesn't match.

Solve it in the lab

Hints

Hint 1

Concatenate text with ||: first_name || ' ' || last_name.

Hint 2

Group by the customer, sort by total descending, then LIMIT 5.

Expected output

Your result should match this, in this order (5 rows).

full_nametotal_spent
Thandi Ndlovu7447.6
Olivia Brown6635
Jamal Wright4420.2
Liam Murphy3859.2
Umar Khan3230.5

Model answer

Show the solution
SELECT c.first_name || ' ' || c.last_name AS full_name,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS total_spent
FROM customers c
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY c.customer_id
ORDER BY total_spent DESC
LIMIT 5;

Other queries that return the same rows are correct too. The lab checks your result, not your exact wording.

Next challenge: Average order value →