Task
You are given order data and a policy change log. Write a PostgreSQL query that compares order volume before and after each policy change, using the policy effective date to split the timeline. Return the policy name, the pre-change order count, the post-change order count, and the percentage change in order volume.
Use the policy effective date as the cutoff, and include only policies that have at least one order in either period. Treat a zero pre-change count as NULL for percentage change to avoid division by zero.
Schema
| table | column | type | description |
|---|
| policies | policy_id | INT | Primary key for the policy |
| policies | policy_name | VARCHAR(100) | Name of the policy change |
| policies | effective_date | DATE | Date the policy took effect |
| orders | order_id | INT | Primary key for the order |
| orders | policy_id | INT | Foreign key to policies.policy_id |
| orders | order_created_at | TIMESTAMP | When the order was created |
| orders | order_status | VARCHAR(30) | Order status at creation time |
Sample data
| policies | policy_id | policy_name | effective_date |
|---|
| policies | 1 | Free Returns Update | 2024-03-10 |
| policies | 2 | Shipping Fee Change | 2024-04-01 |
| policies | 3 | Seller Verification Rule | 2024-04-15 |
| orders | order_id | policy_id | order_created_at | order_status |
|---|
| orders | 101 | 1 | 2024-03-02 10:15:00 | completed |
| orders | 102 | 1 | 2024-03-10 09:00:00 | completed |
| orders | 103 | 1 | 2024-03-18 14:20:00 | completed |
| orders | 104 | 2 | 2024-03-20 08:05:00 | completed |
| orders | 105 | 2 | 2024-04-02 11:30:00 | completed |
| orders | 106 | 2 | 2024-04-18 16:45:00 | canceled |
| orders | 107 | 3 | 2024-04-01 12:10:00 | completed |
| orders | 108 | 3 | 2024-04-16 09:40:00 | completed |
| orders | 109 | 3 | 2024-04-20 19:55:00 | completed |
| orders | 110 | 3 | 2024-04-20 20:05:00 | null |
Expected output
| policy_name | pre_change_orders | post_change_orders | pct_change |
|---|
| Free Returns Update | 1 | 2 | 100.00 |
| Shipping Fee Change | 1 | 2 | 100.00 |
| Seller Verification Rule | 1 | 3 | 200.00 |
