Holiday Airport Traffic Surge

Start Timer

0:00:00

Upvote
2
Downvote
Save question
Mark as completed
View comments (1)

The airline analytics team wants to evaluate airport congestion before Christmas by comparing flight activity across two consecutive years. Specifically, they are analyzing the number of departing flights from each origin_airport during the 10-day window of December 15 through December 24 in both 2023 and 2024.

Write a query to compute, for each origin airport:

  1. flights_2023: count of departing flights in the 2023 window.
  2. flights_2024: count of departing flights in the 2024 window.
  3. pct_increase: percentage increase from 2023 to 2024, rounded to two decimals.

Note:

  • Sort the results in descending order of pct_increase.

  • You may assume that there was at least one flight from each origin airport in 2023 within the requested date.

Schema

Input:

flights table

Column Type
flight_id INTEGER
origin_airport VARCHAR
destination_airport VARCHAR
departure_time DATETIME

Output:

Column Type
origin_airport VARCHAR
flights_2023 INTEGER
flights_2024 INTEGER
pct_increase DECIMAL

Example:

Input:

flights table

flight_id origin_airport destination_airport departure_time
1 SFO JFK 2023-12-15 09:00:00
2 SFO LAX 2023-12-18 14:00:00
3 SFO DEN 2024-12-16 12:00:00
4 SFO SEA 2024-12-20 08:00:00
5 SFO ORD 2024-12-22 19:00:00
6 JFK LAX 2023-12-20 10:00:00
7 JFK ATL 2024-12-17 11:00:00
8 JFK BOS 2024-12-19 13:00:00
9 JFK MIA 2024-12-24 15:00:00

(All timestamps fall within the respective Dec 15–24 windows.)

Output:

origin_airport flights_2023 flights_2024 pct_increase
JFK 1 3 200.00
SFO 2 3 50.00

Explanation:

SFO

  • 2023 flights: 2 (Dec 15 & Dec 18)
  • 2024 flights: 3 (Dec 16, 20, 22)
  • pct increase = (3 − 2) / 2 × 100 = 50.00%

JFK

  • 2023 flights: 1 (Dec 20)
  • 2024 flights: 3 (Dec 17, 19, 24)
  • pct increase = (3 − 1) / 1 × 100 = 200.00%

Both airports meet the 50% threshold and appear in the output.

.
.
.
.
.


Comments