Performance Metrics Over Time

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.
-- || ----- Flight Fields ----- ||
-- , fli.flight_number

-- || ----- Departure Airport Fields ----- ||
, 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
-- , fli.departure_terminal
, fli.departure_gate

-- || ----- Arrival Airport Fields ----- ||
-- , 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
-- , fli.arrival_terminal
-- , fli.arrival_gate

-- || ----- Carrier Feilds ----- ||
-- , ca.name as operator_name
-- , ca.carrier_id as operator_id

-- || ----- Fleet Feilds ----- ||
, fle.aircraft_family
-- , fle.aircraft_type_series
-- , fle.engine_master_series

-- || ----- Friendly Feilds ----- ||
-- , '(' || 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

, COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 THEN 1 ELSE 0 END) 
- SUM(CASE WHEN ((fli.actual_gate_departure IS NULL AND fli.actual_runway_departure IS NULL) 
OR (fli.actual_gate_arrival IS NULL AND fli.actual_runway_arrival IS NULL))
AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) as comp_tracked_flights

, COUNT(*) - SUM(CASE WHEN (fli.actual_gate_departure IS NULL AND fli.actual_runway_departure IS NULL AND (fli.is_diverted = 0 AND fli.is_cancelled = 0)) THEN 1 ELSE 0 END) 
- SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 THEN 1 ELSE 0 END) as dep_tracked_flights

, COUNT(*) - SUM(CASE WHEN (fli.actual_gate_arrival IS NULL AND fli.actual_runway_arrival IS NULL AND (fli.is_diverted = 0 AND fli.is_cancelled = 0)) THEN 1 ELSE 0 END) 
- SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 THEN 1 ELSE 0 END) as arr_tracked_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 OR fli.actual_runway_departure IS NOT NULL) AND (fli.actual_gate_arrival IS NOT NULL OR fli.actual_runway_departure 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 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) AS d0s
, SUM(CASE WHEN fli.gate_departure_delay <= 5 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) AS d5s
, SUM(CASE WHEN fli.gate_departure_delay <= 14 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) AS d14s
, SUM(CASE WHEN fli.gate_departure_delay <= 15 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) AS d15s
, SUM(CASE WHEN fli.gate_departure_delay <= 30 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) AS d30s
, SUM(CASE WHEN fli.gate_departure_delay <= 60 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) AS d60s
, SUM(CASE WHEN fli.gate_departure_delay <= 120 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) AS d120s
, SUM(CASE WHEN fli.gate_departure_delay <= -15 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) 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 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) AS a0s
, SUM(CASE WHEN fli.gate_arrival_delay <= 5 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) AS a5s
, SUM(CASE WHEN fli.gate_arrival_delay <= 14 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) AS a14s
, SUM(CASE WHEN fli.gate_arrival_delay <= 15 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) AS a15s
, SUM(CASE WHEN fli.gate_arrival_delay <= 30 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) AS a30s
, SUM(CASE WHEN fli.gate_arrival_delay <= 60 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) AS a60s
, SUM(CASE WHEN fli.gate_arrival_delay <= 120 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) AS a120s
, SUM(CASE WHEN fli.gate_arrival_delay <= -15 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) 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

WHERE 
-- fli.scheduled_gate_departure BETWEEN to_date('05/01/2021', 'DD/MM/YYYY') AND to_date('10/02/2021', 'DD/MM/YYYY') -- Select flights in a given date range
-- AND 
fli.scheduled_gate_departure >= dateadd('month', -3, getdate()) -- Select flight with dates in the last X months, years, days etc
-- AND
-- ca.carrier_id IN ('DL', 'AA') -- Filter by carriers
AND
da.airport_id IN ('ATL') -- Filter by departure airports
-- AND
-- aa.airport_id IN ('LAX') -- Filter by arrival airports
-- AND
-- da.country_code = aa.country_code -- Domestic Flights Only

GROUP BY date
-- || ----- Flight Fields ----- ||
-- , fli.flight_number

-- || ----- Departure Airport Fields ----- ||
, da.name
, da.airport_id
, da.state_code
, da.country_code
-- , fli.departure_terminal
, fli.departure_gate

-- || ----- Arrival Airport Fields ----- ||
-- , 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
-- , fli.arrival_terminal
-- , fli.arrival_gate

-- || ----- Carrier Feilds ----- ||
-- , ca.name as operator_name
-- , ca.carrier_id as operator_id

-- || ----- Fleet Feilds ----- ||
, fle.aircraft_family
-- , fle.aircraft_type_series
-- , fle.engine_master_series
;

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