Flight Data — Route Reliability Ranking

Flight Data benchmark · all Flight Data queries

Routes ranked by reliability (on-time + low cancellation)

Representative SQL

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

SELECT
  f.origin,
  f.dest,
  ao.city AS origin_city,
  ad.city AS dest_city,
  COUNT(*) AS total_scheduled,
  SUM(CASE WHEN f.cancelled = 1 THEN 1 ELSE 0 END) AS cancelled_count,
  ROUND(100.0 * SUM(CASE WHEN f.cancelled = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS cancellation_rate_pct,
  SUM(CASE WHEN f.cancelled = 0 AND f.arr_delay <= 15 THEN 1 ELSE 0 END) AS ontime_count,
  ROUND(
    100.0 * SUM(CASE WHEN f.cancelled = 0 AND f.arr_delay <= 15 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN f.cancelled = 0 THEN 1 ELSE 0 END), 0),
    2
  ) AS ontime_pct,
  ROUND(AVG(f.distance), 0) AS distance_miles
FROM flights AS f
LEFT JOIN airports AS ao
  ON f.origin = ao.code
LEFT JOIN airports AS ad
  ON f.dest = ad.code
WHERE
  f.flight_date >= '2021-08-01' AND f.flight_date < '2025-01-01'
GROUP BY
  f.origin,
  f.dest,
  ao.city,
  ad.city
HAVING
  COUNT(*) >= 100
ORDER BY
  ontime_pct DESC,
  cancellation_rate_pct ASC,
  f.origin,
  f.dest
LIMIT 30

Representative DataFrame

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

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

    ao = airports.select(col("code").alias("ao_code"), col("city").alias("origin_city"))
    ad = airports.select(col("code").alias("ad_code"), col("city").alias("dest_city"))

    return (
        flights.join(ao, left_on="origin", right_on="ao_code", how="left")
        .join(ad, left_on="dest", right_on="ad_code", how="left")
        .filter(_date_condition(col, lit))
        .with_columns(
            (col("cancelled") == lit(1)).cast(int).alias("_cancelled"),
            ((col("cancelled") == lit(0)) & (col("arr_delay") <= lit(15))).cast(int).alias("_ontime"),
            (col("cancelled") == lit(0)).cast(int).alias("_operated"),
        )
        .group_by("origin", "dest", "origin_city", "dest_city")
        .agg(
            col("flight_id").count().alias("total_scheduled"),
            col("_cancelled").sum().alias("cancelled_count"),
            col("_ontime").sum().alias("ontime_count"),
            col("_operated").sum().alias("_non_cancelled"),
            col("distance").mean().alias("_avg_dist"),
        )
        .filter(col("total_scheduled") >= lit(100))
        .with_columns(
            ctx.when(col("_non_cancelled") > lit(0))
            .then(col("_non_cancelled"))
            .otherwise(lit(None))
            .alias("_non_cancelled_nz"),
        )
        .with_columns(
            _round_half_away(ctx, lit(100.0) * col("cancelled_count") / col("total_scheduled"), 2).alias(
                "cancellation_rate_pct"
            ),
            _round_half_away(ctx, lit(100.0) * col("ontime_count") / col("_non_cancelled_nz"), 2).alias("ontime_pct"),
            _round_half_away(ctx, col("_avg_dist"), 0).alias("distance_miles"),
        )
        .select(
            "origin",
            "dest",
            "origin_city",
            "dest_city",
            "total_scheduled",
            "cancelled_count",
            "cancellation_rate_pct",
            "ontime_count",
            "ontime_pct",
            "distance_miles",
        )
        .sort(
            ["ontime_pct", "cancellation_rate_pct", "origin", "dest"],
            descending=[True, False, False, False],
            nulls_last=True,
        )
        .limit(30)
    )

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()['route-reliability'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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