Customer Lifetime Spend
Walmart
Problem Statement
Walmart marketing teams want to identify high-value customers by looking at their total lifetime spend.
Write an SQL query to calculate the total amount spent by each customer since they signed up for the Walmart+ program.
Rules:
- Include all customers from the Customers table, even those who have never placed an order.
- For customers with no orders, the total spend should be 0.
- Output columns: customer_name, total_lifetime_spend.
- Results must be sorted by total_lifetime_spend in descending order.
Tests your understanding of
Aggregation, Joining and COALESCE
Input Tables
Customers
| customer_id(INTEGER) | customer_name(VARCHAR) | signup_date(DATE) |
|---|---|---|
| 1 | John Doe | 2024-01-01 |
| 2 | Jane Smith | 2024-01-05 |
| 3 | Bob Wilson | 2024-02-10 |
| 4 | Alice Brown | 2024-02-15 |
| 5 | Charlie Davis | 2024-03-01 |
| 6 | Eve Miller | 2024-03-05 |
Orders
| order_id(INTEGER) | customer_id(INTEGER) | order_amount(DECIMAL) | order_date(DATE) |
|---|---|---|---|
| 101 | 1 | 150.5 | 2024-01-10 |
| 102 | 1 | 50 | 2024-02-01 |
| 103 | 2 | 300.25 | 2024-01-20 |
| 104 | 3 | 120 | 2024-03-01 |
| 105 | 1 | 75 | 2024-03-10 |
| 106 | 4 | 500 | 2024-03-15 |
Returns
| return_id(INTEGER) | order_id(INTEGER) | return_reason(VARCHAR) |
|---|---|---|
| 1 | 102 | Damaged |
| 2 | 105 | Wrong item |
Expected Output
| customer_name(VARCHAR) | total_lifetime_spend(DECIMAL) |
|---|---|
| Alice Brown | 500 |
| Jane Smith | 300.25 |
| John Doe | 275.5 |
| Bob Wilson | 120 |
| Charlie Davis | 0 |
| Eve Miller | 0 |
Tags
MediumAggregationJoiningCOALESCE
10-15 min
70%