Your question is SQL Segmentation by Outcome. 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.
How would you use SQL to segment orders by region, retailer, and delivery outcome?
Use the provided orders, regions, and retailers data. Classify each order's delivery outcome from its status and timing, while retaining orders with missing region or retailer references.
region_name, retailer_name, delivery_outcome, order_count, total_order_value, and avg_delivery_minutesUnknown, and sort by region, retailer, and outcome| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| region_id | INT | Region reference |
| retailer_id | INT | Retailer reference |
| order_value | DECIMAL(10,2) | Order value in dollars |
| delivery_status | VARCHAR(20) | Recorded delivery status |
| placed_at | TIMESTAMP | Order placement timestamp |
| promised_at | TIMESTAMP | Promised delivery timestamp |
| delivered_at | TIMESTAMP | Actual delivery timestamp |
| Column | Type | Description |
|---|---|---|
| region_idPK | INT | Unique region identifier |
| region_name | VARCHAR(50) | Region display name |
| Column | Type | Description |
|---|---|---|
| retailer_idPK | INT | Unique retailer identifier |
| retailer_name | VARCHAR(80) | Retailer display name |