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.
| 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 |
| 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.
| period | completed_picks | avg_pick_duration_minutes |
|---|---|---|
| after | 5 | 14.80 |
| before | 4 | 18.38 |
| Column | Type | Description |
|---|---|---|
| pick_idPK | 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 |
{"shopper_picks":[["4","102","5004","2024-03-28","completed","20.00"],["1","101","5001","2024-03-25","completed","18.50"],["9","105","5009","2024-04-05","canceled","13.00"],["2","101","5002","2024-03-26","completed","16.00"],["11","107","5011","2024-04-07","completed",null],["6","103","5006","2024-04-02","completed","15.00"],["3","102","5003","2024-03-30","canceled","22.00"],["8","104","5008","2024-04-04","completed","16.00"],["5","103","5005","2024-03-31","completed","19.00"],["12","108","5012"Output[["after","4","15.50"],["before","5","17.50"]]