Hourly Monitoring

This following SQL will produce a rolling aggregation of how the flight frequency and performance of an airport and the carriers operating within it changes each hour of the day.

The interval in which the results are aggregated can be changed by altering the datepart string of the date_trunc function.

SELECT date_trunc('month', fl.scheduled_gate_departure) as flight_date
, ca.name as operator_carrier
, ai.name as airport_name
, extract(hour from fl.scheduled_gate_departure) as hour_of_flight
, COUNT(*) as total_flights
, COUNT(*) - SUM(CASE WHEN fl.is_diverted = 1 OR fl.is_cancelled = 1 THEN 1 ELSE 0 END) as completed_flights
, SUM(CASE WHEN fl.is_diverted = 1 OR fl.is_cancelled = 1 THEN 1 ELSE 0 END) as not_completed_flights
, SUM(CASE WHEN fl.is_cancelled = 1 THEN 1 ELSE 0 END) as cancelled_flights
, SUM(CASE WHEN fl.is_diverted = 1 THEN 1 ELSE 0 END) as diverted_flights
, SUM(fle.total_seat_count) as total_seats
, SUM(CASE WHEN fl.is_diverted = 0 and fl.is_cancelled = 0 THEN fle.total_seat_count ELSE 0 END) AS completed_seats
, SUM(CASE WHEN fl.is_diverted = 1 OR fl.is_cancelled = 1 THEN fle.total_seat_count ELSE 0 END) as not_completed_seats
, SUM(CASE WHEN fl.is_cancelled = 1 THEN fle.total_seat_count ELSE 0 END) as cancelled_seats
, SUM(CASE WHEN fl.is_diverted = 1 THEN fle.total_seat_count ELSE 0 END) as diverted_seats
, MIN(fl.actual_taxi_out_time) as min_taxi
, MAX(fl.actual_taxi_out_time) as max_taxi
, AVG(fl.actual_taxi_out_time) as avg_taxi
, STDDEV(fl.actual_taxi_out_time) as std_taxi
, 'departure' as direction
FROM flights as fl
INNER JOIN airports as ai
ON fl.departure_airport_id = ai.airport_id
INNER JOIN carriers as ca
ON fl.operating_carrier_id = ca.carrier_id
INNER JOIN fleets as fle
ON fl.fleet_aircraft_id = fle.aircraft_id

GROUP BY flight_date
, operator_carrier
, airport_name
, hour_of_flight

UNION

SELECT date_trunc('month', fl.scheduled_gate_arrival) as flight_date
, ca.name as operator_carrier
, ai.name as airport_name
, extract(hour from fl.scheduled_gate_arrival) as hour_of_flight
, COUNT(*) as total_flights
, COUNT(*) - SUM(CASE WHEN fl.is_diverted = 1 OR fl.is_cancelled = 1 THEN 1 ELSE 0 END) as completed_flights
, SUM(CASE WHEN fl.is_diverted = 1 OR fl.is_cancelled = 1 THEN 1 ELSE 0 END) as not_completed_flights
, SUM(CASE WHEN fl.is_cancelled = 1 THEN 1 ELSE 0 END) as cancelled_flights
, SUM(CASE WHEN fl.is_diverted = 1 THEN 1 ELSE 0 END) as diverted_flights
, SUM(fle.total_seat_count) as total_seats
, SUM(CASE WHEN fl.is_diverted = 0 and fl.is_cancelled = 0 THEN fle.total_seat_count ELSE 0 END) AS completed_seats
, SUM(CASE WHEN fl.is_diverted = 1 OR fl.is_cancelled = 1 THEN fle.total_seat_count ELSE 0 END) as not_completed_seats
, SUM(CASE WHEN fl.is_cancelled = 1 THEN fle.total_seat_count ELSE 0 END) as cancelled_seats
, SUM(CASE WHEN fl.is_diverted = 1 THEN fle.total_seat_count ELSE 0 END) as diverted_seats
, MIN(fl.actual_taxi_in_time) as min_taxi
, MAX(fl.actual_taxi_in_time) as max_taxi
, AVG(fl.actual_taxi_in_time) as avg_taxi
, STDDEV(fl.actual_taxi_in_time) as std_taxi
, 'arrival' as direction
FROM flights as fl
INNER JOIN airports as ai
ON fl.arrival_airport_id = ai.airport_id
INNER JOIN carriers as ca
ON fl.operating_carrier_id = ca.carrier_id
INNER JOIN fleets as fle
ON fl.fleet_aircraft_id = fle.aircraft_id

GROUP BY flight_date
, operator_carrier
, airport_name
, hour_of_flight

ORDER BY flight_date
, airport_name
, operator_carrier;

If you have any feedback or ideas about Cirium Sky Warehouse then we would love to hear from you via the Cirium Help Desk.