Table: Flights
+-------------------+------+ | Column Name | Type | +-------------------+------+ | departure_airport | int | | arrival_airport | int | | flights_count | int | +-------------------+------+ (departure_airport, arrival_airport) is the primary key column (combination of columns with unique values) for this table. Each row of this table indicates that there were flights_count flights that departed from departure_airport and arrived at arrival_airport.
Write a solution to report the ID of the airport with the most traffic. The airport with the most traffic is the airport that has the largest total number of flights that either departed from or arrived at the airport. If there is more than one airport with the most traffic, report them all.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Flights table: +-------------------+-----------------+---------------+ | departure_airport | arrival_airport | flights_count | +-------------------+-----------------+---------------+ | 1 | 2 | 4 | | 2 | 1 | 5 | | 2 | 4 | 5 | +-------------------+-----------------+---------------+ Output: +------------+ | airport_id | +------------+ | 2 | +------------+ Explanation: Airport 1 was engaged with 9 flights (4 departures, 5 arrivals). Airport 2 was engaged with 14 flights (10 departures, 4 arrivals). Airport 4 was engaged with 5 flights (5 arrivals). The airport with the most traffic is airport 2.
Example 2:
Input: Flights table: +-------------------+-----------------+---------------+ | departure_airport | arrival_airport | flights_count | +-------------------+-----------------+---------------+ | 1 | 2 | 4 | | 2 | 1 | 5 | | 3 | 4 | 5 | | 4 | 3 | 4 | | 5 | 6 | 7 | +-------------------+-----------------+---------------+ Output: +------------+ | airport_id | +------------+ | 1 | | 2 | | 3 | | 4 | +------------+ Explanation: Airport 1 was engaged with 9 flights (4 departures, 5 arrivals). Airport 2 was engaged with 9 flights (5 departures, 4 arrivals). Airport 3 was engaged with 9 flights (5 departures, 4 arrivals). Airport 4 was engaged with 9 flights (4 departures, 5 arrivals). Airport 5 was engaged with 7 flights (7 departures). Airport 6 was engaged with 7 flights (7 arrivals). The airports with the most traffic are airports 1, 2, 3, and 4.
Problem Overview: You are given a flights table where each row represents traffic between two airports. The goal is to compute the total traffic handled by each airport (both departures and arrivals) and return the airport with the highest combined traffic.
Approach 1: Separate Aggregations for Departures and Arrivals (O(n) time, O(k) space)
A straightforward method aggregates traffic twice: once by departure_airport and once by arrival_airport. Each query calculates the sum of flights_count using GROUP BY. After computing both totals, you combine them and aggregate again per airport to get the final traffic value. This approach works but introduces extra steps because the same table is scanned multiple times.
The core operations rely on SQL aggregation functions such as SUM() and grouping logic from SQL. Time complexity is O(n) since every row must be processed at least once. Space complexity is O(k), where k is the number of unique airports stored during grouping.
Approach 2: UNION ALL + Single Aggregation (Optimal) (O(n) time, O(k) space)
The cleaner solution converts arrivals and departures into a single stream of traffic records. Use UNION ALL to stack two projections of the table: one mapping departure_airport to airport traffic and another mapping arrival_airport. Each row keeps its flights_count. After combining them, run a single GROUP BY airport_id and compute SUM(flights_count) to get total traffic per airport.
This works because UNION ALL preserves every record and avoids deduplication overhead. The database engine processes one aggregated dataset instead of multiple intermediate joins. Finally, order the result by total traffic in descending order and return the top airport. The query still runs in O(n) time with O(k) grouping space, making it the most efficient and readable approach.
The logic relies heavily on relational operations from Database query design and grouping techniques like GROUP BY aggregation.
Recommended for interviews: The UNION ALL + GROUP BY strategy is the expected solution. It demonstrates strong SQL fundamentals and clean data transformation thinking. The multi-aggregation method shows you understand the problem, but the single-pass aggregation approach is what interviewers usually look for because it is concise, scalable, and idiomatic SQL.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Separate Aggregations for Departures and Arrivals | O(n) | O(k) | When you want a straightforward breakdown of departure and arrival traffic before merging results |
| UNION ALL + Single GROUP BY (Optimal) | O(n) | O(k) | Best general solution; concise SQL with a single aggregation step |
Top K Frequent Elements - Bucket Sort - Leetcode 347 - Python • NeetCode • 665,789 views views
Watch 9 more video solutions →Practice The Airport With the Most Traffic with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor