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.
Output
- One row per region, retailer, and delivery outcome
- Columns:
region_name, retailer_name, delivery_outcome, order_count, total_order_value, and avg_delivery_minutes
- Include all orders, label missing dimension values as
Unknown, and sort by region, retailer, and outcome
Output details
- Classify delivered orders as
delivered_on_time when delivered_at is at or before promised_at, otherwise classify them as delivered_late.
- Classify statuses
canceled and failed as canceled and failed; classify every other status as pending.
- Compute
avg_delivery_minutes only for delivered outcomes, using elapsed minutes from placement to delivery, rounded to two decimals; return NULL otherwise.
- Sort
region_name, retailer_name, and delivery_outcome in ascending order.
