Task
You are given ACME House customer activity data from the ACME House storefront. Write a PostgreSQL query that compares each customer’s behavior in their most recent active month with their own prior 3-month baseline. Return one row per customer-month where the month is the customer’s latest month with at least one completed order.
Your result should include the customer, the latest active month, current-month order count and spend, the average monthly order count and spend across the previous 3 active calendar months, and the change versus that baseline. Exclude cancelled orders from all calculations, and only keep customers who have at least 3 prior months available for comparison.
Schema
| Column | Type | Description |
|---|
| order_id | INT | Unique order identifier |
| customer_id | INT | Customer identifier |
| order_date | DATE | Date the order was placed |
| order_status | VARCHAR(20) | Order status |
| order_amount | NUMERIC(10,2) | Order value |
| channel | VARCHAR(20) | Purchase channel in ACME House |
Sample data
| order_id | customer_id | order_date | order_status | order_amount | channel |
|---|
| 1004 | 101 | 2024-04-09 | completed | 200.00 | web |
| 1008 | 102 | 2024-04-21 | completed | 40.00 | app |
| 1015 | 103 | 2024-04-18 | completed | 300.00 | showroom |
| 1020 | 104 | 2024-03-12 | cancelled | 90.00 | web |
Expected output
| customer_id | activity_month | current_order_count | current_spend | prior_3mo_avg_order_count | prior_3mo_avg_spend | order_count_change | spend_change |
|---|
| 101 | 2024-04-01 | 2 | 260.00 | 1.33 | 126.67 | 0.67 | 133.33 |
| 102 | 2024-04-01 | 1 | 40.00 | 1.00 | 70.00 | 0.00 | -30.00 |
| 103 | 2024-04-01 | 1 | 300.00 | 1.00 | 180.00 | 0.00 | 120.00 |
