Flight Data — Delay Cause Breakdown

Flight Data benchmark · all Flight Data queries

Distribution of delay causes across all delayed flights

Representative SQL

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

SELECT
  SUM(CASE WHEN f.carrier_delay > 0 THEN 1 ELSE 0 END) AS carrier_delay_count,
  ROUND(AVG(CASE WHEN f.carrier_delay > 0 THEN f.carrier_delay END), 2) AS avg_carrier_delay,
  SUM(CASE WHEN f.weather_delay > 0 THEN 1 ELSE 0 END) AS weather_delay_count,
  ROUND(AVG(CASE WHEN f.weather_delay > 0 THEN f.weather_delay END), 2) AS avg_weather_delay,
  SUM(CASE WHEN f.nas_delay > 0 THEN 1 ELSE 0 END) AS nas_delay_count,
  ROUND(AVG(CASE WHEN f.nas_delay > 0 THEN f.nas_delay END), 2) AS avg_nas_delay,
  SUM(CASE WHEN f.security_delay > 0 THEN 1 ELSE 0 END) AS security_delay_count,
  ROUND(AVG(CASE WHEN f.security_delay > 0 THEN f.security_delay END), 2) AS avg_security_delay,
  SUM(CASE WHEN f.late_aircraft_delay > 0 THEN 1 ELSE 0 END) AS late_aircraft_count,
  ROUND(AVG(CASE WHEN f.late_aircraft_delay > 0 THEN f.late_aircraft_delay END), 2) AS avg_late_aircraft_delay,
  COUNT(*) AS total_delayed_flights
FROM flights AS f
WHERE
  f.cancelled = 0
  AND f.arr_delay > 15
  AND f.flight_date >= '2021-08-01'
  AND f.flight_date < '2025-01-01'

Representative DataFrame

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

def delay_causes_expression_impl(ctx: DataFrameContext) -> Any:
    flights, col, lit = _date_window(ctx, lambda col, lit: (col("cancelled") == lit(0), col("arr_delay") > lit(15)))

    prepared = flights.with_columns(
        (col("carrier_delay") > lit(0)).cast(int).fill_null(0).alias("_carrier_flag"),
        ctx.when(col("carrier_delay") > lit(0)).then(col("carrier_delay")).otherwise(lit(None)).alias("_carrier_pos"),
        (col("weather_delay") > lit(0)).cast(int).fill_null(0).alias("_weather_flag"),
        ctx.when(col("weather_delay") > lit(0)).then(col("weather_delay")).otherwise(lit(None)).alias("_weather_pos"),
        (col("nas_delay") > lit(0)).cast(int).fill_null(0).alias("_nas_flag"),
        ctx.when(col("nas_delay") > lit(0)).then(col("nas_delay")).otherwise(lit(None)).alias("_nas_pos"),
        (col("security_delay") > lit(0)).cast(int).fill_null(0).alias("_security_flag"),
        ctx.when(col("security_delay") > lit(0))
        .then(col("security_delay"))
        .otherwise(lit(None))
        .alias("_security_pos"),
        (col("late_aircraft_delay") > lit(0)).cast(int).fill_null(0).alias("_late_flag"),
        ctx.when(col("late_aircraft_delay") > lit(0))
        .then(col("late_aircraft_delay"))
        .otherwise(lit(None))
        .alias("_late_pos"),
    )
    totals = prepared.select(
        col("_carrier_flag").sum().alias("carrier_delay_count"),
        col("_carrier_pos").mean().alias("_avg_carrier"),
        col("_weather_flag").sum().alias("weather_delay_count"),
        col("_weather_pos").mean().alias("_avg_weather"),
        col("_nas_flag").sum().alias("nas_delay_count"),
        col("_nas_pos").mean().alias("_avg_nas"),
        col("_security_flag").sum().alias("security_delay_count"),
        col("_security_pos").mean().alias("_avg_security"),
        col("_late_flag").sum().alias("late_aircraft_count"),
        col("_late_pos").mean().alias("_avg_late"),
        col("flight_id").count().alias("total_delayed_flights"),
    )
    return totals.with_columns(
        _round_half_away(ctx, col("_avg_carrier"), 2).alias("avg_carrier_delay"),
        _round_half_away(ctx, col("_avg_weather"), 2).alias("avg_weather_delay"),
        _round_half_away(ctx, col("_avg_nas"), 2).alias("avg_nas_delay"),
        _round_half_away(ctx, col("_avg_security"), 2).alias("avg_security_delay"),
        _round_half_away(ctx, col("_avg_late"), 2).alias("avg_late_aircraft_delay"),
    ).select(
        "carrier_delay_count",
        "avg_carrier_delay",
        "weather_delay_count",
        "avg_weather_delay",
        "nas_delay_count",
        "avg_nas_delay",
        "security_delay_count",
        "avg_security_delay",
        "late_aircraft_count",
        "avg_late_aircraft_delay",
        "total_delayed_flights",
    )

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-causes'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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