SQL for Terminal Performance | Dataford Interview Questions

Write a SQL query to analyze United Road Logistics terminal performance by day, including on-time delivery rate, total shipments, and average dwell time.

Use shipment delivery date as the reporting day. Calculate on-time rate only among shipments with delivery and promised dates, and calculate dwell time only when arrival and departure timestamps are available.

Output

One row per terminal and reporting day, excluding shipments without a delivery date.
Columns: terminal_name, performance_date, on_time_delivery_rate, total_shipments, and average_dwell_hours.
Return rates and dwell hours rounded to two decimals, ordered by terminal name and date.

Schema

terminals

Column	Type	Description
terminal_idPK	INT	Unique terminal identifier
terminal_name	VARCHAR(100)	United Road Logistics terminal name

shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment identifier
terminal_id	INT	Terminal associated with the shipment
delivery_date	DATE	Shipment delivery reporting date
arrived_at	TIMESTAMP	Shipment arrival timestamp at the terminal
departed_at	TIMESTAMP	Shipment departure timestamp from the terminal
delivered_at	TIMESTAMP	Actual delivery timestamp
promised_delivery_at	TIMESTAMP	Promised delivery timestamp

Your question is SQL for Terminal Performance. 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