Task
You are given the task of measuring whether an operational change improved fulfillment performance in Instacart Shopper App picking workflows. Write a SQL query that compares average picking time before and after the change date for each period, using only the shopper_picks table. Return one row per period labeled before or after, along with the number of completed picks and the average pick duration in minutes. Only include completed picks with a non-null duration.
Schema
| column_name | type | description |
|---|
| pick_id | INT | Unique pick event identifier |
| shopper_id | INT | Shopper who completed the pick |
| batch_id | INT | Fulfillment batch identifier |
| pick_date | DATE | Date of the pick event |
| status | VARCHAR(20) | Pick status such as completed or canceled |
| pick_duration_minutes | DECIMAL(6,2) | Time spent picking in minutes |
Sample data
| pick_id | shopper_id | batch_id | pick_date | status | pick_duration_minutes |
|---|
| 4 | 102 | 5004 | 2024-03-28 | completed | 20.00 |
| 1 | 101 | 5001 | 2024-03-25 | completed | 18.50 |
| 7 | 104 | 5007 | 2024-04-03 | completed | 14.00 |
| 10 | 106 | 5010 | 2024-04-06 | completed | 17.00 |
Assume the operational change went live on 2024-04-01, so dates before that are before and dates on or after that are after.
Expected output
| period | completed_picks | avg_pick_duration_minutes |
|---|
| after | 5 | 14.80 |
| before | 4 | 18.38 |
