SELECT date_trunc('month', 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
    , ca.name as operator_name
    , ca.carrier_id as operator_id
    , fle.aircraft_type_series
    , 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
    , AVG(fli.scheduled_block_time) as avg_scheduled_block
    , AVG(fli.actual_block_time) as avg_actual_block
    , AVG(fli.block_time_overshoot) as avg_block_overshoot
    , 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.block_time_overshoot <= 0 THEN 1 ELSE 0 END) as b0s
    , AVG(fli.gate_departure_delay) as average_departure_delay
	, SUM(CASE WHEN fli.gate_departure_delay <= 0 THEN 1 ELSE 0 END) AS d0s
    , 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
    , AVG(fli.gate_arrival_delay) as average_departure_delay
	, SUM(CASE WHEN fli.gate_arrival_delay <= 0 THEN 1 ELSE 0 END) AS a0s
    , 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
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
    , ca.name
    , ca.carrier_id
    , fle.aircraft_type_series;