Route Share Over Time (Fleets Included)

Each flight has been given standard industry metrics based on Cirium's standard definitions.

These flags and other attributes are then aggregated by the date they occurred on. This can be adjusted by changing the datepart string in the date_trunc function. This provides a rolling performance for each route over time.

Performance metric / completed flights, will give the percentage of flights hitting a particular benchmark.

SELECT date_trunc('day', fli.published_departure) as date -- Modify the datepart string to adjust the interval of the date series.
, da.name as departure_airport_name
, da.airport_id as departure_airport_id
, da.state_code as departure_state
, da.country_code as departure_country
, aa.name as arrival_airport_name
, aa.airport_id as arrival_airport_id
, aa.state_code as arrival_state
, aa.country_code as arrival_country
, fle.aircraft_family
, fle.aircraft_type
, fle.aircraft_type_series
, ca.name as operator_name
, ca.carrier_id as operator_id
, '(' || ca.carrier_id || ') ' || ca.name as operator_name_friendly
, '(' || da.airport_id || ') ' || da.name || ', ' || da.state_code || ', ' || da.country_code as departure_airport_friendly
, COUNT(*) as total_flights
, COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 THEN 1 ELSE 0 END) as completed_flights
, MIN(CASE WHEN da.country_code = aa.country_code THEN 1 ELSE 0 END) as is_domestic
, SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 THEN 1 ELSE 0 END) as not_completed_flights
, SUM(CASE WHEN fli.is_cancelled = 1 THEN 1 ELSE 0 END) as cancelled_flights
, SUM(CASE WHEN fli.is_diverted = 1 THEN 1 ELSE 0 END) as diverted_flights
, SUM(fle.total_seat_count) as total_seats
, SUM(CASE WHEN fli.is_diverted = 0 and fli.is_cancelled = 0 THEN fle.total_seat_count ELSE 0 END) AS completed_seats
, SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 THEN fle.total_seat_count ELSE 0 END) as not_completed_seats
, SUM(CASE WHEN fli.is_cancelled = 1 THEN fle.total_seat_count ELSE 0 END) as cancelled_seats
, SUM(CASE WHEN fli.is_diverted = 1 THEN fle.total_seat_count ELSE 0 END) as diverted_seats
, SUM(CASE WHEN fli.block_time_overshoot <= 0 THEN 1 ELSE 0 END) as b0s
, SUM(CASE WHEN fli.actual_gate_departure IS NOT NULL AND fli.actual_gate_arrival IS NOT NULL THEN 1 ELSE 0 END) as CFs
, AVG(fli.gate_departure_delay) as average_departure_delay
, MIN(fli.gate_departure_delay) as min_departure_delay
, MIN(fli.actual_taxi_out_time) as min_taxi_out
, SUM(CASE WHEN fli.gate_departure_delay <= 0 THEN 1 ELSE 0 END) AS d0s
, SUM(CASE WHEN fli.gate_departure_delay <= 5 THEN 1 ELSE 0 END) AS d5s
, SUM(CASE WHEN fli.gate_departure_delay <= 14 THEN 1 ELSE 0 END) AS d14s
, SUM(CASE WHEN fli.gate_departure_delay <= 15 THEN 1 ELSE 0 END) AS d15s
, SUM(CASE WHEN fli.gate_departure_delay <= 30 THEN 1 ELSE 0 END) AS d30s
, SUM(CASE WHEN fli.gate_departure_delay <= 60 THEN 1 ELSE 0 END) AS d60s
, SUM(CASE WHEN fli.gate_departure_delay <= 120 THEN 1 ELSE 0 END) AS d120s
, SUM(CASE WHEN fli.gate_departure_delay <= -15 THEN 1 ELSE 0 END) AS dNeg15s
, AVG(fli.gate_arrival_delay) as average_departure_delay
, MIN(fli.gate_arrival_delay) as min_departure_delay
, MIN(fli.actual_taxi_in_time) as min_taxi_in
, SUM(CASE WHEN fli.gate_arrival_delay <= 0 THEN 1 ELSE 0 END) AS a0s
, SUM(CASE WHEN fli.gate_arrival_delay <= 5 THEN 1 ELSE 0 END) AS a5s
, SUM(CASE WHEN fli.gate_arrival_delay <= 14 THEN 1 ELSE 0 END) AS a14s
, SUM(CASE WHEN fli.gate_arrival_delay <= 15 THEN 1 ELSE 0 END) AS a15s
, SUM(CASE WHEN fli.gate_arrival_delay <= 30 THEN 1 ELSE 0 END) AS a30s
, SUM(CASE WHEN fli.gate_arrival_delay <= 60 THEN 1 ELSE 0 END) AS a60s
, SUM(CASE WHEN fli.gate_arrival_delay <= 120 THEN 1 ELSE 0 END) AS a120s
, SUM(CASE WHEN fli.gate_arrival_delay <= -15 THEN 1 ELSE 0 END) AS aNeg15s
from flights as fli
INNER JOIN airports da
ON fli.departure_airport_id = da.airport_id
INNER JOIN airports aa
ON fli.arrival_airport_id = aa.airport_id
INNER JOIN fleets fle
ON fli.fleet_aircraft_id = fle.aircraft_id
INNER JOIN carriers ca
ON fli.operating_carrier_id = ca.carrier_id

GROUP BY date
, departure_airport_name
, ca.name
, da.airport_id
, da.state_code
, da.country_code
, aa.name
, aa.airport_id
, aa.state_code
, aa.country_code
, fle.aircraft_family
, fle.aircraft_type
, fle.aircraft_type_series
, ca.name
, ca.carrier_id;

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