-- 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
    ;