Performance Metrics

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

This can be joined to the flight_extended view via the flight id, or to airport information via the arrival and departure airport ids. Likewise, operator information can be joined via the operating_carrier_id.

The flights can then be grouped by dates, airports or carriers. At which point, dividing the number of completed flights by the number of a performance flag (d0 for example), will provid the percentage of flights that have hit that metric for the chosen grouping.

An example of this can be found in the provided Performance Benchmarks

SELECT fli.flight_id
, fli.published_departure
, fli.departure_airport_id
, fli.arrival_airport_id
, fli.operating_carrier_id
, fli.is_cancelled
, fli.operating_carrier_id
, fli.block_time_overshoot
, CASE WHEN fli.block_time_overshoot <= 0 THEN 1 ELSE 0 END as b0
, CASE WHEN fli.actual_gate_departure IS NOT NULL AND fli.actual_gate_arrival IS NOT NULL THEN 1 ELSE 0 END as CF
, fli.gate_departure_delay
, CASE WHEN fli.gate_departure_delay <= 0 THEN 1 ELSE 0 END AS d0
, CASE WHEN fli.gate_departure_delay <= 5 THEN 1 ELSE 0 END AS d5
, CASE WHEN fli.gate_departure_delay <= 14 THEN 1 ELSE 0 END AS d14
, CASE WHEN fli.gate_departure_delay <= 15 THEN 1 ELSE 0 END AS d15
, CASE WHEN fli.gate_departure_delay <= 30 THEN 1 ELSE 0 END AS d30
, CASE WHEN fli.gate_departure_delay <= 60 THEN 1 ELSE 0 END AS d60
, CASE WHEN fli.gate_departure_delay <= 120 THEN 1 ELSE 0 END AS d120
, CASE WHEN fli.gate_departure_delay <= -15 THEN 1 ELSE 0 END AS dNeg15
, fli.gate_arrival_delay
, CASE WHEN fli.gate_arrival_delay <= 0 THEN 1 ELSE 0 END AS a0
, CASE WHEN fli.gate_arrival_delay <= 0 THEN 1 ELSE 0 END AS a5
, CASE WHEN fli.gate_arrival_delay <= 14 THEN 1 ELSE 0 END AS a14
, CASE WHEN fli.gate_arrival_delay <= 15 THEN 1 ELSE 0 END AS a15
, CASE WHEN fli.gate_arrival_delay <= 30 THEN 1 ELSE 0 END AS a30
, CASE WHEN fli.gate_arrival_delay <= 60 THEN 1 ELSE 0 END AS a60
, CASE WHEN fli.gate_arrival_delay <= 120 THEN 1 ELSE 0 END AS a120
, CASE WHEN fli.gate_arrival_delay <= -15 THEN 1 ELSE 0 END AS aNeg15
from flights as fli ;

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.