SQL for On-Time Delivery Trends | Dataford Interview Questions

Write a SQL query to analyze on-time delivery performance by site, carrier, and week, including late-delivery rate and trend over time.

Use completed deliveries only. A delivery is late when its actual delivery date is after its promised delivery date.

Output

One row per site, carrier, and calendar week.
Include site_name, carrier_name, week_start, delivery_count, late_delivery_count, late_delivery_rate, and late_rate_change_pp.
Show the prior-week rate change in percentage points, or NULL when no prior week exists for that site and carrier.
Order by site, carrier, and week.

deliveries

Column	Type	Description
delivery_idPK	INT	Unique delivery identifier
site_id	INT	Operational site handling the delivery
carrier_id	INT	Carrier responsible for the delivery
promised_delivery_date	DATE	Date by which delivery was promised
actual_delivery_date	DATE	Date delivery was completed

sites

carriers

Your question is SQL for On-Time Delivery Trends. 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.