SQL for Delivery Performance | Dataford Interview Questions

Write a SQL query to analyze weekly order volume, on-time delivery rate, and backorders by customer segment for Bunzl’s operations.

Use the orders, customers, and deliveries tables. Include all order weeks and treat missing customer segments as Unknown.

Output

One row per order week and customer segment.
Columns: week_start, customer_segment, order_volume, on_time_delivery_rate, and backorder_count.
Include delivery rate as a percentage of delivered orders, rounded to two decimals, and return NULL when no orders were delivered.
Sort by week_start, then customer_segment.

customers

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
backorder_qty	INT	Quantity currently backordered

deliveries

Column	Type	Description
delivery_idPK	INT	Unique delivery record identifier
order_id	INT	Order associated with the delivery
promised_date	DATE	Promised delivery date
delivered_date	DATE	Actual delivery date, or NULL if not delivered

Your question is SQL for Delivery Performance. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.