Flight Data — Average Departure Delay by Origin Airport

Flight Data benchmark · all Flight Data queries

Average departure delay in minutes by origin airport

Representative SQL

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

SELECT
  f.origin,
  ap.name AS airport_name,
  ap.city,
  ap.state,
  COUNT(*) AS total_flights,
  ROUND(AVG(f.dep_delay), 2) AS avg_dep_delay,
  ROUND(AVG(f.arr_delay), 2) AS avg_arr_delay,
  SUM(CASE WHEN f.dep_delay > 15 THEN 1 ELSE 0 END) AS delayed_flights
FROM flights AS f
LEFT JOIN airports AS ap
  ON f.origin = ap.code
WHERE
  f.cancelled = 0
  AND NOT f.dep_delay IS NULL
  AND f.flight_date >= '2021-08-01'
  AND f.flight_date < '2025-01-01'
GROUP BY
  f.origin,
  ap.name,
  ap.city,
  ap.state
HAVING
  COUNT(*) >= 100
ORDER BY
  avg_dep_delay DESC,
  f.origin
LIMIT 50

Representative DataFrame

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

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

    ap = airports.select(
        col("code").alias("ap_code"),
        col("name").alias("airport_name"),
        col("city"),
        col("state"),
    )

    return (
        flights.join(ap, left_on="origin", right_on="ap_code", how="left")
        .filter(
            _date_condition(
                ctx.col, ctx.lit, lambda col, lit: (col("cancelled") == lit(0), col("dep_delay").is_not_null())
            )
        )
        .with_columns((col("dep_delay") > lit(15)).cast(int).alias("_delayed"))
        .group_by("origin", "airport_name", "city", "state")
        .agg(
            col("flight_id").count().alias("total_flights"),
            col("dep_delay").mean().alias("_avg_dep"),
            col("arr_delay").mean().alias("_avg_arr"),
            col("_delayed").sum().alias("delayed_flights"),
        )
        .with_columns(
            _round_half_away(ctx, col("_avg_dep"), 2).alias("avg_dep_delay"),
            _round_half_away(ctx, col("_avg_arr"), 2).alias("avg_arr_delay"),
        )
        .select(
            "origin",
            "airport_name",
            "city",
            "state",
            "total_flights",
            "avg_dep_delay",
            "avg_arr_delay",
            "delayed_flights",
        )
        .filter(col("total_flights") >= lit(100))
        .sort(["avg_dep_delay", "origin"], descending=[True, False])
        .limit(50)
    )

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()['delay-by-airport'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('flightdata', 'delay-by-airport', family="expression").source)  # or family="pandas"

Source

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