Delta Air Lines Interview Questions

Delta Airlines is analyzing how passengers travel across its domestic network. The analytics team wants to understand which routes are most commonly flown out of each airport, and how long these popular journeys usually take. You’re working as a data analyst at Delta Airlines, and you’re provided with a table flights where each row represents one scheduled flight.

Write a SQL query to return the top 5 most frequent flight routes for each origin, ordered first by frequency descending, and then (for ties) by average journey duration ascending.

Notes:

1. A route is defined as a unique (origin, destination) pair.
2. Assume there is no more then 5 routes with the same frequency and average filght time.

Schema:

Input:

flights

Output:

Example:

Input Data

flights

Output

Explanation:

From each origin (JFK, LAX), the query returns the top 5 most frequent destination routes. Since each origin has only two routes in this dataset, they are all included. For routes with the same frequency, sorting is done by increasing average duration.