Task
SwiftCart wants a simple delivery performance breakdown from its orders table. Write a PostgreSQL query that uses a CASE statement to segment each order into a delivery outcome bucket, then returns the number of orders in each bucket.
Requirements
- Create a derived column called
delivery_outcome using these rules:
Delivered when delivered_at is not null and delivered_at <= promised_dateDelivered Late when delivered_at is not null and delivered_at > promised_dateCancelled when status = 'cancelled'In Progress for all other rows
- Return
delivery_outcome and order_count
- Group by the derived outcome and sort by
order_count descending, then delivery_outcome ascending
Table Definition
| column_name | type | description |
|---|
| order_id | INT | Primary key for the order |
| customer_name | VARCHAR(100) | Customer name |
| status | VARCHAR(20) | Current order status |
| order_date | DATE | Date the order was placed |
| promised_date | DATE | Expected delivery date |
| delivered_at | DATE | Actual delivery date, null if not delivered |
Sample Data
| order_id | customer_name | status | order_date | promised_date | delivered_at |
|---|
| 101 | Ava Patel | delivered | 2024-03-01 | 2024-03-05 | 2024-03-04 |
| 102 | Liam Chen | delivered | 2024-03-01 | 2024-03-04 | 2024-03-06 |
| 103 | Noah Kim | cancelled | 2024-03-02 | 2024-03-06 | null |
| 104 | Emma Ross | shipped | 2024-03-03 | 2024-03-07 | null |
| 105 | Olivia Reed | delivered | 2024-03-03 | 2024-03-08 | 2024-03-08 |
| 106 | Mia Lopez | cancelled | 2024-03-04 | 2024-03-09 | null |
Expected Output
| delivery_outcome | order_count |
|---|
| Delivered | 4 |
| Cancelled | 3 |
| In Progress | 3 |
| Delivered Late | 2 |
