Task
You are given Instacart-style order fulfillment data across regions and retailers. Write a PostgreSQL query that returns the worst-performing retailer in each region for January 2024, based on fulfillment rate. Define fulfillment rate as the percentage of delivered orders out of all non-cancelled orders. Exclude retailers with fewer than 2 non-cancelled orders in the month, and rank retailers within each region from worst to best using a ranking function.
Schema
regions
| column | type | description |
|---|
| region_id | INT | Primary key for the region |
| region_name | VARCHAR(50) | Region name |
retailers
| column | type | description |
|---|
| retailer_id | INT | Primary key for the retailer |
| retailer_name | VARCHAR(100) | Retailer name |
| region_id | INT | Region the retailer belongs to |
orders
| column | type | description |
|---|
| order_id | INT | Primary key for the order |
| retailer_id | INT | Retailer that received the order |
| order_date | DATE | Date the order was placed |
| order_status | VARCHAR(20) | Order outcome |
Sample data
| order_id | retailer_id | order_date | order_status |
|---|
| 101 | 1 | 2024-01-05 | delivered |
| 102 | 1 | 2024-01-06 | failed |
| 103 | 2 | 2024-01-07 | delivered |
| 104 | 2 | 2024-01-08 | delivered |
| 105 | 3 | 2024-01-09 | failed |
Expected output
| region_name | retailer_name | non_cancelled_orders | delivered_orders | fulfillment_rate | performance_rank |
|---|
| Northeast | FreshMart | 3 | 1 | 33.33 | 1 |
| South | GreenBasket | 2 | 1 | 50.00 | 1 |
| West | DailyFoods | 2 | 1 | 50.00 | 1 |
