-- This SQL will produce a rolling aggregation of how the flight frequency and performance of an airport and the carriers operating within it changes
-- each hour of the day. The interval in which the results are aggregated can be changed by altering the datepart string of the date_trunc function.

SELECT date_trunc('month', fl.scheduled_gate_departure) as flight_date
	, ca.name as operator_carrier
    , ai.name as airport_name
	, extract(hour from fl.scheduled_gate_departure) as hour_of_flight
    , COUNT(*) as total_flights
    , COUNT(*) - SUM(CASE WHEN fl.is_diverted = 1 OR fl.is_cancelled = 1 THEN 1 ELSE 0 END) as completed_flights
    , SUM(CASE WHEN fl.is_diverted = 1 OR fl.is_cancelled = 1 THEN 1 ELSE 0 END) as not_completed_flights
    , SUM(CASE WHEN fl.is_cancelled = 1 THEN 1 ELSE 0 END) as cancelled_flights
    , SUM(CASE WHEN fl.is_diverted = 1 THEN 1 ELSE 0 END) as diverted_flights
    , SUM(fle.total_seat_count) as total_seats
    , SUM(CASE WHEN fl.is_diverted = 0 and fl.is_cancelled = 0 THEN fle.total_seat_count ELSE 0 END) AS completed_seats
    , SUM(CASE WHEN fl.is_diverted = 1 OR fl.is_cancelled = 1 THEN fle.total_seat_count ELSE 0 END) as not_completed_seats
    , SUM(CASE WHEN fl.is_cancelled = 1 THEN fle.total_seat_count ELSE 0 END) as cancelled_seats
    , SUM(CASE WHEN fl.is_diverted = 1 THEN fle.total_seat_count ELSE 0 END) as diverted_seats
	, MIN(fl.actual_taxi_out_time) as min_taxi
    , MAX(fl.actual_taxi_out_time) as max_taxi
    , AVG(fl.actual_taxi_out_time) as avg_taxi
    , STDDEV(fl.actual_taxi_out_time) as std_taxi
  	, 'departure' as direction
FROM flights as fl
INNER JOIN airports as ai
	ON fl.departure_airport_id = ai.airport_id
INNER JOIN carriers as ca
	ON fl.operating_carrier_id = ca.carrier_id
INNER JOIN fleets as fle
	ON fl.fleet_aircraft_id = fle.aircraft_id
    
GROUP BY flight_date
	, operator_carrier
    , airport_name
    , hour_of_flight

UNION

SELECT date_trunc('month', fl.scheduled_gate_arrival) as flight_date
	, ca.name as operator_carrier
    , ai.name as airport_name
	, extract(hour from fl.scheduled_gate_arrival) as hour_of_flight
    , COUNT(*) as total_flights
    , COUNT(*) - SUM(CASE WHEN fl.is_diverted = 1 OR fl.is_cancelled = 1 THEN 1 ELSE 0 END) as completed_flights
    , SUM(CASE WHEN fl.is_diverted = 1 OR fl.is_cancelled = 1 THEN 1 ELSE 0 END) as not_completed_flights
    , SUM(CASE WHEN fl.is_cancelled = 1 THEN 1 ELSE 0 END) as cancelled_flights
    , SUM(CASE WHEN fl.is_diverted = 1 THEN 1 ELSE 0 END) as diverted_flights
    , SUM(fle.total_seat_count) as total_seats
    , SUM(CASE WHEN fl.is_diverted = 0 and fl.is_cancelled = 0 THEN fle.total_seat_count ELSE 0 END) AS completed_seats
    , SUM(CASE WHEN fl.is_diverted = 1 OR fl.is_cancelled = 1 THEN fle.total_seat_count ELSE 0 END) as not_completed_seats
    , SUM(CASE WHEN fl.is_cancelled = 1 THEN fle.total_seat_count ELSE 0 END) as cancelled_seats
    , SUM(CASE WHEN fl.is_diverted = 1 THEN fle.total_seat_count ELSE 0 END) as diverted_seats
	, MIN(fl.actual_taxi_in_time) as min_taxi
    , MAX(fl.actual_taxi_in_time) as max_taxi
    , AVG(fl.actual_taxi_in_time) as avg_taxi
    , STDDEV(fl.actual_taxi_in_time) as std_taxi
  	, 'arrival' as direction
FROM flights as fl
INNER JOIN airports as ai
	ON fl.arrival_airport_id = ai.airport_id
INNER JOIN carriers as ca
	ON fl.operating_carrier_id = ca.carrier_id
INNER JOIN fleets as fle
	ON fl.fleet_aircraft_id = fle.aircraft_id
    
GROUP BY flight_date
	, operator_carrier
    , airport_name
    , hour_of_flight
    
ORDER BY flight_date
	, airport_name
    , operator_carrier;