SQL Window Functions for Trends | Dataford Interview Questions

Write a SQL query using window functions to analyze operational or revenue trends at Delta Air Lines.

Use the provided flight and ticket sale data. Include completed flights with confirmed ticket sales.

Output

One row per month and origin-destination route.
Return month, origin_airport, destination_airport, monthly_revenue, and revenue_rank.
Rank routes within each month by revenue, highest first, and sort by month, rank, origin, and destination.

flights

Column	Type	Description
flight_idPK	INT	Unique flight identifier
departure_date	DATE	Scheduled departure date
origin_airport	VARCHAR(3)	Three-letter origin airport code
destination_airport	VARCHAR(3)	Three-letter destination airport code
flight_status	VARCHAR(20)	Operational status of the flight

ticket_sales

Column	Type	Description
sale_idPK	INT	Unique ticket sale identifier
flight_id	INT	Referenced flight identifier
amount	NUMERIC(12,2)	Revenue attributed to the sale
sale_status	VARCHAR(20)	Status of the ticket sale

Your question is SQL Window Functions for Trends. 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.