Top Routes SQL Query | Dataford Interview Questions

Write a SQL query to retrieve the top 10 most frequently used bus routes in a given city for Moovit.

Use Lisbon as the city for the supplied data. Count recorded route usage events and return only routes belonging to Lisbon.

Output

One row per used Lisbon route, with route_id, route_code, route_name, and usage_count.
Return at most 10 rows, ordered by usage_count descending, then route_id ascending to break ties.
Routes with no usage events and routes from other cities must be excluded.

cities

Column	Type	Description
city_idPK	INTEGER	Unique city identifier
city_name	VARCHAR(100)	City name used for filtering

routes

route_usage

Column	Type	Description
usage_idPK	INTEGER	Unique route usage event identifier
route_id	INTEGER	Route associated with the usage event

Your question is Top Routes SQL Query. Start with the requirements and the three 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.