Top 5 Underperforming Routes | Dataford Interview Questions

Your question is Top 5 Underperforming Routes. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Amazon Prime Now Operations needs to identify delivery routes with the longest average cycle time. Write a PostgreSQL query that ranks the five slowest routes using completed deliveries during January 2025.

Requirements

Calculate cycle time in minutes as the difference between delivered_at and picked_up_at.
Include only rows with status = 'DELIVERED' and timestamps within January 2025.
Include routes with at least one completed delivery, calculate their average cycle time, and return the five highest averages.
Return route ID, route name, average cycle time, and completed delivery count. Sort by average cycle time descending, then route ID ascending.

Schema

prime_now_routes

Column	Type	Description
route_idPK	INT	Unique Prime Now route identifier
route_name	VARCHAR(80)	Operational route name
service_zone	VARCHAR(40)	Geographic service zone

prime_now_deliveries

Column	Type	Description
delivery_idPK	INT	Unique delivery identifier
route_id	INT	Route used for the delivery
picked_up_at	TIMESTAMP	Timestamp when the order left the station
delivered_at	TIMESTAMP	Timestamp when the order reached the customer
status	VARCHAR(20)	Delivery lifecycle status