SQL for Delivery Performance | Dataford Interview Questions

Write a SQL query to join orders, customers, and shipments, then calculate weekly on-time delivery rate by region at Adepta Partners.

Use the shipment delivery date to determine the week. Include delivered shipments only, and consider a shipment on time when its delivery date is on or before its promised date.

Output

One row per delivery week and customer region.
Columns: week_start, region, delivered_orders, on_time_orders, and on_time_rate_pct.
Sort by week_start ascending, then region ascending.

Schema

customers

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

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

shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment identifier
order_id	INT	Order associated with the shipment
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.

Problem