Skip to main content
Talentd

The Airport With the Most Traffic - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min read
Practice this problem

Problem Statement

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.

Approach Overview

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.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Separate Aggregations for Departures and ArrivalsO(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

Video Solution

Top K Frequent Elements - Bucket Sort - Leetcode 347 - Python • NeetCode • 665,789 views views

Watch 9 more video solutions →

Frequently Asked Questions

Is The Airport With the Most Traffic easy or hard?
The problem is rated Medium because it requires understanding how to merge two relational perspectives—departures and arrivals—into one aggregated result. The SQL itself is short, but recognizing the UNION ALL pattern is the key insight.
The Airport With the Most Traffic Python/Java solution
This is primarily a SQL database problem, so the expected solution is written in MySQL or another SQL dialect. In application code such as Python or Java, you could simulate the logic using a hash map that accumulates traffic for each airport.
How to solve The Airport With the Most Traffic in O(n)?
Create two projections of the flights table: one for departure airports and one for arrival airports. Combine them using UNION ALL, then aggregate using GROUP BY airport_id and SUM(flights_count). Finally, sort by total traffic and return the airport with the maximum value.
What is the best approach for The Airport With the Most Traffic?
The best approach uses UNION ALL to combine departures and arrivals into a single dataset, followed by GROUP BY to sum total traffic per airport. This method avoids repeated scans and keeps the query concise. Its time complexity is O(n) with O(k) space for grouping unique airports.
Is The Airport With the Most Traffic asked at Google/Amazon/Meta?
Database aggregation and SQL transformation problems like this frequently appear in interviews at large tech companies including Amazon and Meta. The problem tests GROUP BY usage, UNION ALL, and reasoning about combining datasets.
What data structure is used in The Airport With the Most Traffic?
The problem relies on relational database grouping rather than traditional in-memory data structures. Conceptually, the GROUP BY operation behaves like a hash map where each airport ID stores a running total of traffic.
What is the time complexity of The Airport With the Most Traffic?
The SQL solution runs in O(n) time because each flight record is processed once during aggregation. The GROUP BY operation stores intermediate totals for each airport, resulting in O(k) space where k is the number of unique airports.

Ready to solve this problem?

Practice The Airport With the Most Traffic with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary