-- Each flight 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
-- 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 provide the percentage of flights that have hit that metric for
-- the chosen grouping. An example of this can be in the provided performance_benchmarks SQL.

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