Scenario
A retail company wants to analyze customer purchase behavior and assign membership status based on total spending.
Task
Write a SQL query to produce a summary of each customer's total spending, order count, and membership status.
Requirements
- Join
customers and orders to aggregate spending and order count per customer.
- Assign membership status:
- 'Gold' if total spending is at least 900
- 'Silver' if total spending is at least 500 but less than 900
- 'Bronze' otherwise
- Include all customers, even those without orders (show 0 and 'Bronze' for them).
- Order results by total spending descending.
Table Definitions
customers
| customer_id | INT | Primary key | Customer unique ID |
|---|
| customer_name | VARCHAR(255) | | Customer name |
orders
| order_id | INT | Primary key | Order unique ID |
|---|
| customer_id | INT | Foreign key | Customer placing the order |
| amount | DECIMAL(10,2) | | Order amount |
Sample Data
customers
| customer_id | customer_name |
|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| 4 | Dana |
| 5 | Eve |
orders
| order_id | customer_id | amount |
|---|
| 1 | 1 | 150 |
| 2 | 1 | 300 |
| 3 | 2 | 200 |
| 4 | 3 | 50 |
| 5 | 2 | 600 |
| 6 | 1 | 450 |
| 7 | 5 | 0 |
| 8 | 1 | NULL |
| 9 | 4 | NULL |
Expected Output
| customer_name | total_spent | order_count | membership_status |
|---|
| Alice | 900.00 | 3 | Gold |
| Bob | 800.00 | 2 | Silver |
| Charlie | 50.00 | 1 | Bronze |
| Eve | 0.00 | 1 | Bronze |
| Dana | 0.00 | 0 | Bronze |
