Route Share Over Time

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;

If you have any feedback or ideas about Cirium Sky Data Warehouse then we would love to hear from you via the Cirium Help Desk.