Problem
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_outcomeusing these rules:Deliveredwhendelivered_atis not null anddelivered_at <= promised_dateDelivered Latewhendelivered_atis not null anddelivered_at > promised_dateCancelledwhenstatus = 'cancelled'In Progressfor all other rows
- Return
delivery_outcomeandorder_count - Group by the derived outcome and sort by
order_countdescending, thendelivery_outcomeascending
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 |
Schema
orders
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Primary key for the order |
| customer_name | VARCHAR(100) | Customer name |
| status | VARCHAR(20) | Current order status such as delivered, shipped, processing, or cancelled |
| order_date | DATE | Date the order was placed |
| promised_date | DATE | Expected delivery date |
| delivered_at | DATE | Actual delivery date, null if not delivered |
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
