-- This query will calculate the performance metrics of given grouping values over a specified date range. To add grouping values
-- either uncomment below in both the select statement and the grouping statement. Or add from the flights (fli), fleets (fle), arrival or departure
-- airports (aa & da) or operator information (ca). Ensure these are added to both the select and group statements. Useful example filters, including distinct date
-- ranges are included in the where section.

SELECT
-- || ----- 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 Fields ----- ||
--     , ca.name as operator_name
--     , ca.carrier_id as operator_id

-- || ----- Fleet Fields ----- ||
    , fle.aircraft_family
--     , fle.aircraft_type_series
--     , fle.engine_master_series
--     , fle.aircraft_serial_number

-- || ----- Friendly Fields ----- ||
-- 	, da.name || ' -> ' || aa.name as route
--  , '(' || ca.carrier_id || ') ' || ca.name as operating_airline

    , 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
    , SUM(fle.total_seat_count) as total_seats
	, SUM(CASE WHEN fli.is_cancelled = 1 THEN 1 ELSE 0 END) as cancelled_flights
     
    , 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 AND fli.actual_runway_arrival IS NOT NULL) THEN 1.0 ELSE 0.0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 THEN 1.0 ELSE 0.0 END)), 0) as CF_perc
    
    , SUM(CASE WHEN fli.actual_gate_departure IS NOT NULL OR fli.actual_runway_departure IS NOT NULL THEN 1.0 ELSE 0.0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 THEN 1.0 ELSE 0.0 END)), 0) as dep_CF_perc
    
    , SUM(CASE WHEN fli.actual_gate_arrival IS NOT NULL AND fli.actual_runway_arrival IS NOT NULL THEN 1.0 ELSE 0.0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 THEN 1.0 ELSE 0.0 END)), 0) as arr_CF_perc
            
    , SUM(CASE WHEN fli.gate_departure_delay <= 0 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
                                 OR (fli.actual_gate_departure IS NULL AND fli.actual_runway_departure IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as d0_perc
    
    , SUM(CASE WHEN fli.gate_departure_delay <= 5 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
                                 OR (fli.actual_gate_departure IS NULL AND fli.actual_runway_departure IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as d5_perc
    
    , SUM(CASE WHEN fli.gate_departure_delay <= 14 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
                                 OR (fli.actual_gate_departure IS NULL AND fli.actual_runway_departure IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as d14_perc
    
    , SUM(CASE WHEN fli.gate_departure_delay <= 15 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
                                 OR (fli.actual_gate_departure IS NULL AND fli.actual_runway_departure IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as d15_perc
    
    , SUM(CASE WHEN fli.gate_departure_delay <= 30 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
                                 OR (fli.actual_gate_departure IS NULL AND fli.actual_runway_departure IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as d30_perc
    
    , SUM(CASE WHEN fli.gate_departure_delay <= 60 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
                                 OR (fli.actual_gate_departure IS NULL AND fli.actual_runway_departure IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as d60_perc
    
    , SUM(CASE WHEN fli.gate_departure_delay <= 120 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
                                 OR (fli.actual_gate_departure IS NULL AND fli.actual_runway_departure IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as d120_perc
    
    , AVG(fli.gate_departure_delay) as avg_dep_delay
    
    , SUM(CASE WHEN fli.gate_arrival_delay <= 0 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
                                 OR (fli.actual_gate_arrival IS NULL AND fli.actual_runway_arrival IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as a0_perc
    
    , SUM(CASE WHEN fli.gate_arrival_delay <= 5 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
                                 OR (fli.actual_gate_arrival IS NULL AND fli.actual_runway_arrival IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as a5_perc
            
    , SUM(CASE WHEN fli.gate_arrival_delay <= 14 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
                                 OR (fli.actual_gate_arrival IS NULL AND fli.actual_runway_arrival IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as a14_perc
            
    , SUM(CASE WHEN fli.gate_arrival_delay <= 15 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
                                 OR (fli.actual_gate_arrival IS NULL AND fli.actual_runway_arrival IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as a15_perc
            
    , SUM(CASE WHEN fli.gate_arrival_delay <= 30 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
                                 OR (fli.actual_gate_arrival IS NULL AND fli.actual_runway_arrival IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as a30_perc
            
    , SUM(CASE WHEN fli.gate_arrival_delay <= 60 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
                                 OR (fli.actual_gate_arrival IS NULL AND fli.actual_runway_arrival IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as a60_perc
            
    , SUM(CASE WHEN fli.gate_arrival_delay <= 120 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
    	/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
                                 OR (fli.actual_gate_arrival IS NULL AND fli.actual_runway_arrival IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as a120_perc
    
    , AVG(fli.gate_arrival_delay) as avg_arr_delay
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('day', -20, 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 ('PDX') -- 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  
-- || ----- 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 Fields ----- ||
--     , ca.name as operator_name
--     , ca.carrier_id as operator_id

-- || ----- Fleet Fields ----- ||
    , fle.aircraft_family
--     , fle.aircraft_type_series
--     , fle.engine_master_series
--     , fle.aircraft_serial_number

-- || ----- Friendly Fields ----- ||
-- 	, da.name || ' -> ' || aa.name
--     , '(' || ca.carrier_id || ') ' || ca.name
    ;