Detect Post-Launch Customer Behavior Shifts | Dataford Interview Questions

Your question is Detect Post-Launch Customer Behavior Shifts. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
segment	VARCHAR(50)	Customer segment
signup_date	DATE	Date the customer signed up

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
order_amount	DECIMAL(10,2)	Total order amount
channel	VARCHAR(30)	Purchase channel

product_launches

Column	Type	Description
launch_idPK	INT	Unique launch identifier
product_name	VARCHAR(100)	Product name
launch_date	DATE	Launch date for the product