Flight Data — Busiest Routes by Flight Count

Flight Data benchmark · all Flight Data queries

Top 25 routes ranked by total flight count

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,
  ao.state AS origin_state,
  ad.city AS dest_city,
  ad.state AS dest_state,
  COUNT(*) AS total_flights,
  ROUND(AVG(f.distance), 0) AS avg_distance_miles,
  ROUND(AVG(f.actual_elapsed_time), 0) AS avg_duration_min,
  ROUND(100.0 * SUM(CASE WHEN f.arr_delay <= 15 THEN 1 ELSE 0 END) / COUNT(*), 2) AS ontime_pct
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.cancelled = 0 AND f.flight_date >= '2021-08-01' AND f.flight_date < '2025-01-01'
GROUP BY
  f.origin,
  f.dest,
  ao.city,
  ao.state,
  ad.city,
  ad.state
ORDER BY
  total_flights DESC,
  f.origin,
  f.dest
LIMIT 25

Representative DataFrame

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

def busiest_routes_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"),
        col("state").alias("origin_state"),
    )
    ad = airports.select(
        col("code").alias("ad_code"),
        col("city").alias("dest_city"),
        col("state").alias("dest_state"),
    )

    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, lambda col, lit: col("cancelled") == lit(0)))
        .with_columns((col("arr_delay") <= lit(15)).cast(int).alias("_ontime"))
        .group_by("origin", "dest", "origin_city", "origin_state", "dest_city", "dest_state")
        .agg(
            col("flight_id").count().alias("total_flights"),
            col("distance").mean().alias("_avg_dist"),
            col("actual_elapsed_time").mean().alias("_avg_dur"),
            col("_ontime").sum().alias("_ontime_sum"),
        )
        .with_columns(
            _round_half_away(ctx, col("_avg_dist"), 0).alias("avg_distance_miles"),
            _round_half_away(ctx, col("_avg_dur"), 0).alias("avg_duration_min"),
            _round_half_away(ctx, lit(100.0) * col("_ontime_sum") / col("total_flights"), 2).alias("ontime_pct"),
        )
        .select(
            "origin",
            "dest",
            "origin_city",
            "origin_state",
            "dest_city",
            "dest_state",
            "total_flights",
            "avg_distance_miles",
            "avg_duration_min",
            "ontime_pct",
        )
        .sort(["total_flights", "origin", "dest"], descending=[True, False, False])
        .limit(25)
    )

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()['busiest-routes'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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