Task
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 |
Sample Data
Representative rows are provided below.
Expected Output
| customer_id | customer_name | segment | pre_orders | post_orders | pre_spend | post_spend | order_change | spend_change |
|---|
| 2 | Bob Smith | Growth | 1 | 3 | 80.00 | 285.00 | 2 | 205.00 |
| 1 | Alice Chen | Enterprise | 2 | 1 | 250.00 | 40.00 | -1 | -210.00 |
| 7 | Grace Hall | SMB | 1 | 2 | 90.00 | 220.00 | 1 | 130.00 |
