Task
An online retail company wants to analyze customer spending trends over months. Write a SQL query to calculate the total spending for each customer by month, along with the cumulative spending for each customer over time.
Requirements
- Join the
customers and orders tables on customer_id.
- Group the results by customer and month of the order date.
- Calculate total spending per customer per month.
- Use a window function to calculate cumulative spending for each customer.
Schema
customers (customer_id, customer_name)
orders (order_id, customer_id, order_date, amount)
| customer_id | customer_name |
|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| order_id | customer_id | order_date | amount |
|---|
| 1 | 1 | 2024-01-10 | 100 |
| 2 | 1 | 2024-01-15 | 150 |
| 3 | 2 | 2024-01-12 | 200 |
| 4 | 3 | 2024-01-20 | 300 |
| 5 | 1 | 2024-02-05 | 200 |
| 6 | 2 | 2024-02-10 | 100 |
Expected Output
| customer_name | month | total_spent | cumulative_spending |
|---|
| Alice | 2024-01-01 | 250 | 250 |
| Alice | 2024-02-01 | 200 | 450 |
| Bob | 2024-01-01 | 200 | 200 |
| Bob | 2024-02-01 | 100 | 300 |
| Charlie | 2024-01-01 | 300 | 300 |
