Task
You are given seller onboarding records and order activity. Write a PostgreSQL query that compares each seller’s performance before and after their onboarding change date, and returns the change in average order value and order count across the two periods.
Use the seller’s onboarding change date as the cutoff: orders on or after that date belong to the after period, and orders before it belong to the before period. Return only sellers who have at least one order in both periods.
Schema
| table | column | type | description |
|---|
| seller_onboarding | seller_id | INT | Seller identifier |
| seller_onboarding | seller_name | VARCHAR(255) | Seller display name |
| seller_onboarding | onboarding_change_date | DATE | Date the onboarding change went live |
| orders | order_id | INT | Order identifier |
| orders | seller_id | INT | Seller identifier |
| orders | order_date | DATE | Order date |
| orders | order_amount | DECIMAL(10,2) | Gross order value |
Sample data
| seller_id | seller_name | onboarding_change_date |
|---|
| 1 | North Star Goods | 2024-02-10 |
| 2 | Harbor Finds | 2024-03-01 |
| 3 | Metro Collectibles | 2024-02-20 |
| order_id | seller_id | order_date | order_amount |
|---|
| 101 | 1 | 2024-02-01 | 40.00 |
| 102 | 1 | 2024-02-10 | 55.00 |
| 103 | 1 | 2024-02-18 | 65.00 |
| 104 | 2 | 2024-02-14 | 30.00 |
| 105 | 2 | 2024-03-01 | 45.00 |
| 106 | 2 | 2024-03-08 | 60.00 |
| 107 | 3 | 2024-02-05 | 80.00 |
| 108 | 3 | 2024-02-25 | 95.00 |
| 109 | 3 | 2024-03-02 | 110.00 |
| 110 | 3 | 2024-01-31 | 70.00 |
Expected output
| seller_name | before_order_count | after_order_count | before_avg_order_value | after_avg_order_value | avg_order_value_delta |
|---|
| North Star Goods | 1 | 2 | 40.00 | 60.00 | 20.00 |
| Harbor Finds | 1 | 2 | 30.00 | 52.50 | 22.50 |
| Metro Collectibles | 2 | 2 | 75.00 | 102.50 | 27.50 |
