SQL Running Average Transit Times | Dataford Interview Questions

Write a query using SQL window functions to calculate the running average of shipment transit times.

Use completed shipments only, and include carrier information when available. Transit time is the number of calendar days between pickup and delivery.

Output

One row per completed shipment, with shipment_id, carrier_name, pickup_date, delivered_date, transit_days, and running_avg_transit_days.
Exclude shipments missing either date. Sort by delivered_date, then shipment_id.

Schema

shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment identifier
carrier_id	INT	Identifier of the shipment carrier
pickup_date	DATE	Date the shipment was picked up
delivered_date	DATE	Date the shipment was delivered

carriers

Column	Type	Description
carrier_idPK	INT	Unique carrier identifier
carrier_name	VARCHAR(100)	Carrier display name

Your question is SQL Running Average Transit Times. 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