Segment Orders by Delivery Outcome | Dataford Interview Questions

Your question is Segment Orders by Delivery Outcome. Start with the requirements and the one table 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.

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_outcome using these rules:
- Delivered when delivered_at is not null and delivered_at <= promised_date
- Delivered Late when delivered_at is not null and delivered_at > promised_date
- Cancelled 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

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

Your question is Segment Orders by Delivery Outcome. Start with the requirements and the one table 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.

Requirements

Create a derived column called delivery_outcome using these rules:
- Delivered when delivered_at is not null and delivered_at <= promised_date
- Delivered Late when delivered_at is not null and delivered_at > promised_date
- Cancelled 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

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