Average Transit Time Calculation | Dataford Interview Questions

Given a table of historical port arrivals, how would you calculate the average transit time between two specific ports over the last quarter at Mediterranean Shipping?

Use the historical_port_arrivals table and calculate transit time from departure_at to arrival_at. Include only completed trips between Valencia and Genoa during the previous calendar quarter.

Output

Return one row with origin_port, destination_port, and average_transit_hours.
Round the average to two decimal places.
Exclude incomplete, unrelated, and out-of-period trips.

Schema

historical_port_arrivals

Column	Type	Description
arrival_idPK	INT	Unique historical arrival record identifier
vessel_name	VARCHAR(100)	Name of the vessel completing the voyage
origin_port	VARCHAR(100)	Port from which the voyage departed
destination_port	VARCHAR(100)	Port at which the vessel arrived
departure_at	TIMESTAMP	Departure timestamp from the origin port
arrival_at	TIMESTAMP	Arrival timestamp at the destination port

Your question is Average Transit Time Calculation. Start with the requirements and the one table 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