SQL for Shipment and Delivery Reporting | Dataford Interview Questions

Write a SQL query to report daily shipment volume, on-time delivery rate, and average order cycle time by facility at McKesson.

Use the provided facility, order, and shipment data. Treat on-time rate as the percentage of delivered shipments delivered on or before the promised date. Calculate cycle time only for delivered shipments.

Output

One row per facility and shipment date.
Return facility_code, shipped_date, shipment_volume, on_time_delivery_rate_pct, and avg_order_cycle_time_days.
Include shipment dates represented in the data, sort by facility_code, then shipped_date ascending.

Schema

facilities

Column	Type	Description
facility_idPK	INT	Unique facility identifier
facility_code	VARCHAR(20)	Operational facility code
facility_name	VARCHAR(100)	Facility name
region	VARCHAR(50)	Geographic operating region

orders

Column	Type	Description
order_idPK	INT	Unique customer order identifier
facility_id	INT	Facility fulfilling the order
order_date	DATE	Date the order was placed
priority	VARCHAR(20)	Order service priority

shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment identifier
order_id	INT	Order associated with the shipment
shipped_date	DATE	Date the shipment left the facility
promised_delivery_date	DATE	Committed delivery date
delivered_date	DATE	Actual delivery date

Your question is SQL for Shipment and Delivery Reporting. 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