Airport Hourly Flight Frequency & Peak Profile Report

1. Business Objective & Use Cases

Airport capacity planning, ground handling staffing, gate allocation, and air traffic flow management depend on understanding hourly arrival and departure profiles.

The Airport Hourly Flight Frequency Report calculates the average number of physical aircraft movements (inbound arrivals and outbound departures) for every hour of the day (00:00 to 23:00) by day of the week across a specified date range.

Target Audience

Airport Operations & Slot Managers: Identify peak congestion windows, runway throughput constraints, and apron utilization.
Airline Hub Planners & Station Managers: Optimize ground crew shifts, gate scheduling, and turn times.
Commercial & Retail Strategy: Forecast passenger footfall waves through airport terminals.

2. Executable Natural Language Prompt

Copy and paste the prompt below into the analytics chat bar. Customize the airport list and date range:

PROMPT

Generate an hourly average flight frequency report for the following airports: [List of Airport IATAs, e.g., ATL, BOS, DCA, DFW, DEN, JFK, LAX, LGA, ORD, SEA, SFO] across the date range [Start Date, e.g., 2026-10-01] through [End Date, e.g., 2026-10-31]. Instructions: 1. Scope: Include all own-metal and wet-lease operating flights only (exclude marketing codeshares). 2. Calculation: Group by Airport, Day of Week (Monday–Sunday), and Hour of the Day (0–23). Compute the average number of inbound arrivals and outbound departures per hour across all occurrences of that day of the week in the period. 3. Formatting: Round inbound and outbound hourly averages to the nearest whole number. Filter out any rows where both inbound and outbound rounded averages are 0. 4. Output: Display a sample of the results in chat and export the full dataset to CSV.

3. How the Platform Works Under the Hood

The analytics engine processes SSIM Type 3 schedule records and converts 4-digit military times into discrete 1-hour operational buckets.

flowchart TD
    A[SSIM_3 Schedule Records] --> B[Filter Target Airports & Dates]
    B --> C[Date Expansion & Day-of-Week Matching]
    C --> D[Hour Extraction: DIV local_time, 100]
    D --> E[Aggregate Departures & Arrivals]
    E --> F[Divide by Number of Sample Days]
    F --> G[ROUND and Filter 0/0 Inactive Hours]
    G --> H[Export to CSV]

Technical Invariants & SQL Logic

Dimension	Source Field / Expression	Logic Description
Airport Matching	departure_station / arrival_station	Evaluates outbound flights (departure_station = Airport) and inbound flights (arrival_station = Airport) independently.
Operating Metal Only	operating_airline_disclosure	Filter: operating_airline_disclosure IS NULL OR operating_airline_disclosure = 'X'. Excludes duplicate marketing codeshares (L, S, Z) to ensure exact physical aircraft movements.
Hour Grouping	time_passenger_departure / time_passenger_arrival	Local hour is computed via integer division: CAST(DIV(time_passenger_departure, 100) AS INT64) (e.g., 0745 -> 7, 1930 -> 19).
Day of Week	days_operation + flight_date	Evaluates day of week (Monday = Day 1 through Sunday = Day 7) across actual operating calendar dates in GENERATE_DATE_ARRAY(operation_from, operation_to).
Hourly Averaging	Division by Distinct Days	Average = ROUND(Total Flights in Hour Slot for Day of Week / Count of that Day of Week in Period).
Zero Suppression	Filter Condition	HAVING Inbound_Avg > 0 OR Outbound_Avg > 0.

4. Step-by-Step Customization Recipes

Recipe A: Specific Carrier Hourly Profile To analyze the hub wave structure of a single airline (e.g., Delta at ATL): Generate an hourly average flight frequency report specifically for carrier DL at airport ATL for October 2026. Include rounded inbound and outbound hourly averages by day of the week. Recipe B: Peak Commercial Window (06:00 to 22:00) To eliminate overnight curfew hours and focus strictly on operational hours:

Generate an hourly average flight frequency report for [BOS, LGA, DCA] for November 2026 between the hours of 06:00 and 22:00. Recipe C: Directional Asymmetry Audit To isolate pure departure rush hours: Generate an hourly average flight frequency report for JFK, ORD, and LAX for Q4 2026. Show only hours where the outbound average exceeds the inbound average by 5 or more flights.

5. Expected Output & Interpretation

The engine returns structured tabular records:

Airport	Day of Week	Hour (0–23)	Inbound Average (Arrivals)	Outbound Average (Departures)	Total Hourly Movements
ATL	Monday	6	4	38	42
ATL	Monday	7	18	54	72
ATL	Monday	8	62	22	84
ATL	Monday	9	28	65	93
JFK	Friday	17	24	31	55
JFK	Friday	18	42	19	61

Reading the Profile Departure Waves (Bank Push): High Outbound with low Inbound (e.g., ATL 06:00–07:00) indicates the morning originating bank. Arrival Waves (Bank Inflow): High Inbound with low Outbound (e.g., ATL 08:00) indicates inbound feeder flights landing. Turnaround / Peak Congestion: High Inbound + High Outbound indicates maximum airport surface congestion.

6. Exporting to CSV

Because multi-airport hourly queries typically generate hundreds or thousands of rows (for example, 35 airports × 7 days × 24 hours ≈ 5,880 potential rows), the platform automatically pre-saves the output as an artifact and exports the complete dataset directly to a .csv file.

1. Business Objective & Use Cases​

Target Audience​

2. Executable Natural Language Prompt​

3. How the Platform Works Under the Hood​

4. Step-by-Step Customization Recipes​

5. Expected Output & Interpretation​

6. Exporting to CSV​