SQL for Delivery Performance | Dataford Interview Questions

Write a SQL query to analyze operational performance by joining order, shipment, and customer tables, then calculate delivery time, late shipment rate, and monthly trends.

Use the provided tables and include orders without shipments. Treat a shipment as late when its delivery date is after the promised delivery date. Calculate metrics by order month and customer region.

Output

One row per order month and region
Columns: month_start, region, orders_count, delivered_shipments, avg_delivery_days, late_shipments, and late_shipment_rate
Include undelivered and regionless orders, and sort by month_start, then region

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer account name
region	VARCHAR(50)	Customer operating region

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer associated with the order
order_date	DATE	Date the order was placed
promised_delivery_date	DATE	Expected delivery date

shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment identifier
order_id	INT	Order fulfilled by the shipment
shipped_date	DATE	Date the shipment left the facility
delivered_date	DATE	Date the shipment was 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.

Problem