A retail company wants to analyze customer purchase behavior and assign membership status based on total spending.
Write a SQL query to produce a summary of each customer's total spending, order count, and membership status.
customers and orders to aggregate spending and order count per customer.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 |
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 |
| 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 |