-- Each flight ID 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;