Find the top customer or customers who spent the most money.
Table Schema
| Column | Type | Description |
|---|---|---|
| order_id | integer | Order identifier. |
| customer_id | integer | Customer identifier. |
| order_date | date | Date of the order. |
| Column | Type | Description |
|---|---|---|
| order_id | integer | Order identifier. |
| product_id | integer | Product identifier. |
| quantity | integer | Units bought. |
| price | integer | Price per unit. |
Sample Data
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 101 | 2024-01-01 |
| 2 | 102 | 2024-01-02 |
| 3 | 101 | 2024-01-03 |
| order_id | product_id | quantity | price |
|---|---|---|---|
| 1 | 10 | 2 | 100 |
| 2 | 20 | 1 | 200 |
| 3 | 10 | 1 | 100 |
| customer_id | total_spent |
|---|---|
| 101 | 300 |
SQL Editor
| customer_id | total_spent |
|---|---|
| 101 | 300 |
Hints
Solution
Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.
WITH customer_spending AS (
SELECT
o.customer_id,
SUM(oi.quantity * oi.price) AS total_spent
FROM orders AS o
JOIN order_items AS oi
ON o.order_id = oi.order_id
GROUP BY o.customer_id
),
ranked_customers AS (
SELECT
customer_id,
total_spent,
DENSE_RANK() OVER (ORDER BY total_spent DESC) AS spend_rank
FROM customer_spending
)
SELECT
customer_id,
total_spent
FROM ranked_customers
WHERE spend_rank = 1
ORDER BY customer_id;
Explanation
Join orders with order items and calculate each order's value.
Sum those values by customer to get total spending.
Keep every customer tied for the highest total spent.
Related Questions