Top Routes Per Driver With Ranking | Dataford Interview Questions

Arity Drive analysts need to identify the routes most frequently completed by each driver. Write a PostgreSQL query that aggregates completed trips by driver and route, ranks routes within each driver, and returns the top three ranked routes.

Requirements

Join drivers to trip_events using driver_id.
Consider only events where event_type = 'trip_completed' and both route zones are present.
Count trips for each driver and origin-destination route, then use a ranking window function to return ranks 1 through 3. Keep tied routes at the same rank.
Return results ordered by driver, rank, and route name.

Schema

drivers

Column	Type	Description
driver_idPK	INT	Unique driver identifier
driver_name	VARCHAR(100)	Driver display name

trip_events

Column	Type	Description
event_idPK	INT	Unique trip event identifier
driver_id	INT	Driver associated with the event
event_type	VARCHAR(40)	Event classification
origin_zone	VARCHAR(50)	Trip starting zone
destination_zone	VARCHAR(50)	Trip ending zone
event_timestamp	TIMESTAMP	Time the event occurred

Your question is Top Routes Per Driver With Ranking. 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