SQL for Shipment Performance by Site | Dataford Interview Questions

Write a SQL query to analyze shipment performance by site at Maersk, including on-time delivery rate and average delay by month.

Use the sites and shipments tables. Include delivered shipments with a non-null promised delivery timestamp. A shipment is on time when it is delivered on or before the promised timestamp. Average delay should count early or on-time shipments as zero delay.

Output

One row per site and delivery month.
Columns: site_name, delivery_month, shipment_count, on_time_delivery_rate, and average_delay_days.
Show rates as percentages rounded to two decimals and delays in days rounded to two decimals.
Order by site name, then delivery month ascending.

Schema

sites

Column	Type	Description
site_idPK	INT	Unique Maersk site identifier
site_name	VARCHAR(100)	Maersk site or terminal name

shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment identifier
site_id	INT	Site responsible for the shipment
promised_delivery_at	TIMESTAMP	Promised delivery timestamp
delivered_at	TIMESTAMP	Actual delivery timestamp

Your question is SQL for Shipment Performance by Site. 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