Top 3 Vehicles Per Region by Mileage | Dataford Interview Questions

Write a SQL query to identify the top 3 vehicles per region by monthly mileage using ranking functions.

Use the provided vehicle and mileage log data. Include ties at the third rank, exclude vehicles without a region, and report each vehicle's total mileage for its month.

Output

One row per qualifying vehicle and month with month, region, vehicle_id, monthly_mileage, and vehicle_rank.
Include ranks through 3, ordered by month, region, rank, and vehicle ID.

Schema

vehicles

Column	Type	Description
vehicle_idPK	INT	Unique vehicle identifier
vehicle_model	VARCHAR(50)	Vehicle model name
region	VARCHAR(30)	Operating region assigned to the vehicle
is_active	BOOLEAN	Whether the vehicle is currently active

mileage_logs

Column	Type	Description
log_idPK	INT	Unique mileage log identifier
vehicle_id	INT	Vehicle associated with the log
trip_date	DATE	Date on which mileage was recorded
miles_driven	NUMERIC(10,2)	Miles driven during the logged trip

Your question is Top 3 Vehicles Per Region by Mileage. 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