SQL Shipment Performance by Region | Dataford Interview Questions

Write a SQL query to analyze shipment performance at Convoy by region and week, including on-time rate, average delay, and trend over time.

Use completed shipments only. A shipment is on time when it is delivered on or before its promised delivery timestamp. Average delay is measured in days, where early deliveries have negative values.

Output

One row per region and calendar week with completed shipments
Columns: region_name, week_start, on_time_rate, average_delay_days, and on_time_rate_trend
Rates are percentages rounded to two decimal places; trend is the percentage-point change from the prior week for that region
Exclude shipments without a delivery timestamp or matching region, and order by region_name, then week_start

Schema

regions

Column	Type	Description
region_idPK	INT	Unique region identifier
region_name	VARCHAR(100)	Convoy operating region name

shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment identifier
region_id	INT	Region assigned to the shipment
promised_at	TIMESTAMP	Promised delivery timestamp
delivered_at	TIMESTAMP	Actual delivery timestamp, null when not delivered

Your question is SQL Shipment Performance by Region. Start with the requirements and the two 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