Truck Location Frequency SQL | Dataford Interview Questions

Motive's Fleet Dashboard stores periodic GPS-derived location labels for fleet vehicles. Write a PostgreSQL query that returns the most frequently observed non-null location for each truck.

Requirements

Count location observations for each truck and location.
Rank locations within each truck by observation count descending. Break ties by location name ascending.
Return only trucks with at least one valid location observation, along with the truck's unit number, most frequent location, and observation count.
Order the final results by truck_id ascending.

Schema

trucks

Column	Type	Description
truck_idPK	INT	Unique truck identifier
unit_number	VARCHAR(20)	Fleet unit label
fleet_status	VARCHAR(20)	Current fleet status

truck_locations

Column	Type	Description
location_idPK	INT	Unique location observation identifier
truck_id	INT	Truck associated with the observation
location_name	VARCHAR(100)	Normalized location label
recorded_at	TIMESTAMP	Time of the location observation

Your question is Truck Location Frequency SQL. 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