SQL for Most Active Shipping Routes | Dataford Interview Questions

Write a SQL query to find the most active shipping routes from a database containing millions of transaction records for Phoenix International Freight Services. Treat a route as active when it has completed shipment transactions. Return the five routes with the highest completed shipment counts.

Output

One row per route, with route_id, origin_port, destination_port, and completed_shipments.
Include only routes with at least one completed shipment.
Order by completed_shipments descending, then route_id ascending to break ties.

Schema

routes

Column	Type	Description
route_idPK	INT	Unique shipping route identifier
route_code	VARCHAR(20)	Business code for the route
origin_port	VARCHAR(10)	Origin port code
destination_port	VARCHAR(10)	Destination port code

shipment_transactions

Column	Type	Description
transaction_idPK	BIGINT	Unique shipment transaction identifier
route_id	INT	Referenced shipping route
status	VARCHAR(20)	Current transaction status
transaction_date	DATE	Date the shipment transaction was recorded

Your question is SQL for Most Active Shipping Routes. 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