-- This query provides market share per route on a rolling monthly basis. The first query calculates the total seats and flights for each route
-- over a given time interval. The second query then calculates the seats and flights per route and operator, the two tables are then joined and
-- the market share calculated by dividing the total for each route from the amount of flights/seats the operator has on the route in the given time
-- interval. To change the time interval, change the datepart string in BOTH the date_trunc functions.

WITH total_flights as (
  SELECT COUNT(fl.*) as total_route_flights
  	, date_trunc('month', fl.published_departure) as date -- change datepart string here AND below to change time interval
    , fl.departure_airport_id
  	, da.name as departure_airport_name
    , fl.arrival_airport_id
 	, aa.name as arrival_airport_name
    , fl.departure_airport_id || fl.arrival_airport_id as route
  	, SUM(CASE WHEN fleets.total_seat_count = 0 THEN NULL ELSE fleets.total_seat_count END) as total_route_seats
 FROM flights as fl
  INNER JOIN fleets 
  	ON fl.fleet_aircraft_id = fleets.aircraft_id
  INNER JOIN airports da
  	ON fl.departure_airport_id = da.airport_id
  INNER JOIN airports aa
  	ON fl.arrival_airport_id = aa.airport_id
  
  GROUP BY departure_airport_id
    , arrival_airport_id
  	, date
  	, da.name
	, aa.name
),
daily_routes as (
  SELECT flights.departure_airport_id
    , date_trunc('month', flights.published_departure) as date -- change datepart string here AND above to change time interval
    , flights.arrival_airport_id
    , flights.operating_carrier_id
    , c.name as operator_name
    , flights.departure_airport_id || flights.arrival_airport_id as route
    , COUNT(flights.*) as number_of_flights
    , SUM(CASE WHEN fleets.total_seat_count = 0 THEN NULL ELSE fleets.total_seat_count END) as number_of_seats
  FROM flights
  INNER JOIN carriers as c 
  	ON flights.operating_carrier_id = c.carrier_id
  INNER JOIN fleets 
  	ON flights.fleet_aircraft_id = fleets.aircraft_id
  GROUP BY departure_airport_id
    , arrival_airport_id
    , operating_carrier_id
    , c.name
  	, date
)
SELECT dr.date
  , dr.departure_airport_id
  , tf.departure_airport_name
  , dr.arrival_airport_id
  , tf.arrival_airport_name
  , dr.operator_name
  , dr.route
  , dr.number_of_flights
  , dr.number_of_seats
  , tf.total_route_flights
  , tf.total_route_seats
  , CAST(dr.number_of_flights AS NUMERIC(10, 4)) / tf.total_route_flights as route_share_flights
  , CAST(dr.number_of_seats AS NUMERIC(10, 4)) / tf.total_route_seats as route_share_seats
FROM
  daily_routes dr
  INNER JOIN total_flights tf ON dr.route = tf.route AND dr.date = tf.date;