Task
You are given order data and a policy-change date. Write a PostgreSQL query that finds repeat buyers who made at least two purchases before the policy change but have not purchased on or after that date. Return each buyer once, along with their first purchase date, last pre-change purchase date, and total number of pre-change orders.
Schema
| table | column | type | description |
|---|
| customers | customer_id | INT | Primary key for each buyer |
| customers | customer_name | VARCHAR(255) | Buyer name |
| orders | order_id | INT | Primary key for each order |
| orders | customer_id | INT | Foreign key to customers.customer_id |
| orders | order_date | DATE | Date the order was placed |
| orders | order_status | VARCHAR(50) | Order status such as completed or canceled |
| policy_changes | policy_id | INT | Primary key for each policy event |
| policy_changes | policy_name | VARCHAR(255) | Name of the policy change |
| policy_changes | effective_date | DATE | Date the policy took effect |
Sample data
| customers | customer_id | customer_name |
|---|
| 1 | Ava Chen | |
| 2 | Ben Ortiz | |
| 3 | Cara Singh | |
| 4 | Diego Park | |
| orders | order_id | customer_id | order_date | order_status |
|---|
| 101 | 1 | 2024-01-05 | completed | |
| 102 | 1 | 2024-02-10 | completed | |
| 103 | 1 | 2024-03-04 | completed | |
| 104 | 2 | 2024-01-12 | completed | |
| 105 | 2 | 2024-02-18 | canceled | |
| 106 | 2 | 2024-03-20 | completed | |
| 107 | 3 | 2024-01-08 | completed | |
| 108 | 3 | 2024-02-02 | completed | |
| 109 | 4 | 2024-03-11 | completed | |
| 110 | 4 | 2024-03-25 | completed | |
| policy_changes | policy_id | policy_name | effective_date |
|---|
| 1 | Return window tightened | 2024-03-01 | |
| 2 | Shipping fee update | 2024-02-15 | |
Expected output
| customer_id | customer_name | first_purchase_date | last_pre_change_purchase_date | pre_change_order_count |
|---|
| 1 | Ava Chen | 2024-01-05 | 2024-02-10 | 2 |
| 3 | Cara Singh | 2024-01-08 | 2024-02-02 | 2 |
