-
Notifications
You must be signed in to change notification settings - Fork 8
/
2112. The Airport With the Most Traffic.py
124 lines (87 loc) · 3.12 KB
/
2112. The Airport With the Most Traffic.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
2112. The Airport With the Most Traffic
Medium
26
6
Add to List
Share
SQL Schema
Table: Flights
+-------------------+------+
| Column Name | Type |
+-------------------+------+
| departure_airport | int |
| arrival_airport | int |
| flights_count | int |
+-------------------+------+
(departure_airport, arrival_airport) is the primary key column 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 an SQL query 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 query 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.
/* Write your T-SQL query statement below */
with cte
as
(
SELECT departure_airport as airport_id, flights_count
FROM Flights
union all
SELECT arrival_airport , flights_count
FROM Flights
)
select top 1 with ties airport_id
from (
select airport_id,sum(flights_count) as total
from cte
group by airport_id
)x
order by total desc