Window Functions for Top Routes | Dataford Interview Questions

Write a SQL query using window functions to find the top three busiest flight routes by passenger volume for each month.

Use valid flight records with non-null dates, airport codes, and non-negative passenger counts. Passenger volume is the total passengers across all flights on the same route during a calendar month.

Output

One row per route included in the monthly top three.
Columns: month, origin_airport, destination_airport, passenger_volume, and route_rank.
Sort by month ascending, route rank ascending, origin airport, and destination airport.

Schema

flight_segments

Column	Type	Description
flight_idPK	INTEGER	Unique flight segment identifier
flight_date	DATE	Date on which the flight operated
origin_airport	VARCHAR(3)	Three-letter origin airport code
destination_airport	VARCHAR(3)	Three-letter destination airport code
passenger_count	INTEGER	Number of passengers recorded for the flight

Your question is Window Functions for Top Routes. Start with the requirements and the one table 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