Your question is Top Shoppers by Fulfillment Time. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Instacart's Shopper Operations team wants to identify top-performing shoppers based on how quickly they fulfill customer orders. Using June 30, 2024 as the reporting date, write a PostgreSQL query that evaluates completed orders from the rolling 30-day window ending on that date.
Fulfillment time is measured from picked_at to delivered_at. Use RANK() so shoppers with equal averages receive the same rank.
| Column | Type | Description |
|---|---|---|
| shopper_idPK | INT | Unique shopper identifier |
| shopper_name | VARCHAR(100) | Shopper display name |
| city | VARCHAR(80) | Primary operating city |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| shopper_id | INT | Assigned shopper identifier |
| order_date | DATE | Date the order was placed |
| status | VARCHAR(30) | Order lifecycle status |
| Column | Type | Description |
|---|---|---|
| fulfillment_idPK | INT | Unique fulfillment record identifier |
| order_id | INT | Related order identifier |
| picked_at | TIMESTAMP | Timestamp when shopping was completed |
| delivered_at | TIMESTAMP | Timestamp when the order was delivered |
| status | VARCHAR(30) | Fulfillment processing status |