SQL: Average Transit Time | Dataford Interview Questions

Write a SQL query to find the average transit time of vehicles moving between inspection centers and local delivery hubs at Carvana.

Use the facility and transit movement data provided. Include only completed, positive-duration movements whose origin is an inspection center and destination is a local delivery hub.

Output

One row per inspection center and local delivery hub pair
Columns: inspection_center, delivery_hub, and avg_transit_hours
Round average hours to two decimal places and order by inspection_center, then delivery_hub

Schema

facilities

Column	Type	Description
facility_idPK	INT	Unique facility identifier
facility_name	VARCHAR(100)	Display name of the facility
facility_type	VARCHAR(40)	Facility classification

transit_movements

Column	Type	Description
movement_idPK	INT	Unique transit movement identifier
vehicle_id	VARCHAR(20)	Vehicle identifier
origin_facility_id	INT	Facility where the movement began
destination_facility_id	INT	Facility where the movement ended
departed_at	TIMESTAMP	Vehicle departure timestamp
arrived_at	TIMESTAMP	Vehicle arrival timestamp

Your question is SQL: Average Transit Time. 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