NovaCart launched a new product on 2024-04-01 and wants to identify customers whose purchasing behavior changed afterward. Write a SQL query to compare each customer's activity in the 30 days before launch vs. the 30 days after launch.
Requirements
- For each customer, calculate
pre_orders, post_orders, pre_spend, and post_spend using orders in the windows 2024-03-02 to 2024-03-31 and 2024-04-01 to 2024-04-30.
- Return only customers who had at least 1 order in both periods.
- Flag a customer as changed if either order count changed by at least 2 or total spend changed by at least 100.
- Include the customer's segment and sort by absolute spend change descending, then
customer_id.
Table Definitions
customers
| column | type | description |
|---|
| customer_id | INT | Customer identifier |
| customer_name | VARCHAR(100) | Customer name |
| segment | VARCHAR(50) | Customer segment |
| signup_date | DATE | Account creation date |
orders
| column | type | description |
|---|
| order_id | INT | Order identifier |
| customer_id | INT | Customer who placed the order |
| order_date | DATE | Order date |
| order_amount | DECIMAL(10,2) | Order value |
| channel | VARCHAR(30) | Purchase channel |
product_launches
| column | type | description |
|---|
| launch_id | INT | Launch identifier |
| product_name | VARCHAR(100) | Product name |
| launch_date | DATE | Product launch date |
