Flight Data — Carrier Market Share by Route

Flight Data benchmark · all Flight Data queries

Top carriers ranked by flight volume and market share

Representative SQL

Rendered in the benchmark’s native SQL with default parameters (no dialect translation available).

WITH carrier_totals AS (
  SELECT
    f.reporting_airline,
    a.name AS airline_name,
    COUNT(*) AS flight_count,
    COUNT(DISTINCT f.origin || '-' || f.dest) AS routes_served,
    ROUND(AVG(f.distance), 0) AS avg_distance_miles
  FROM flights AS f
  LEFT JOIN airlines AS a
    ON f.reporting_airline = a.code
  WHERE
    f.cancelled = 0 AND f.flight_date >= '2021-08-01' AND f.flight_date < '2025-01-01'
  GROUP BY
    f.reporting_airline,
    a.name
), total AS (
  SELECT
    SUM(flight_count) AS grand_total
  FROM carrier_totals
)
SELECT
  ct.reporting_airline,
  ct.airline_name,
  ct.flight_count,
  ct.routes_served,
  ct.avg_distance_miles,
  ROUND(100.0 * ct.flight_count / t.grand_total, 2) AS market_share_pct
FROM carrier_totals AS ct
CROSS JOIN total AS t
ORDER BY
  ct.flight_count DESC,
  ct.reporting_airline
LIMIT 20

Representative DataFrame

Expression family (Polars, PySpark, DataFusion), default parameters.

def market_share_expression_impl(ctx: DataFrameContext) -> Any:
    flights = ctx.get_table("flights")
    col, lit = ctx.col, ctx.lit

    carrier_totals = (
        flights.join(_airline_names(ctx), left_on="reporting_airline", right_on="a_code", how="left")
        .filter(_date_condition(col, lit, lambda col, lit: col("cancelled") == lit(0)))
        .with_columns((col("origin") + lit("-") + col("dest")).alias("route"))
        .group_by("reporting_airline", "airline_name")
        .agg(
            col("flight_id").count().alias("flight_count"),
            col("route").n_unique().alias("routes_served"),
            col("distance").mean().alias("_avg_dist"),
        )
    )

    return (
        carrier_totals.with_columns(col("flight_count").sum().alias("grand_total"))
        .with_columns(
            _round_half_away(ctx, lit(100.0) * col("flight_count") / col("grand_total"), 2).alias("market_share_pct"),
            _round_half_away(ctx, col("_avg_dist"), 0).alias("avg_distance_miles"),
        )
        .select(
            "reporting_airline",
            "airline_name",
            "flight_count",
            "routes_served",
            "avg_distance_miles",
            "market_share_pct",
        )
        .sort(["flight_count", "reporting_airline"], descending=[True, False])
        .limit(20)
    )

Get the exact query for your run

The blocks above are representative. To get the precise statement a specific platform runs at a specific scale, substitute your own values:

from benchbox import FlightData

bench = FlightData(scale_factor=1.0)            # your scale factor
print(bench.get_queries()['market-share'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('flightdata', 'market-share', family="expression").source)  # or family="pandas"

Source

Query text: benchbox/core/flightdata/queries.py