Flight Data — Weather Delay Impact by Month

Flight Data benchmark · all Flight Data queries

Average weather delay by month showing seasonal patterns

Representative SQL

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

SELECT
  f.month,
  COUNT(*) AS total_flights,
  SUM(CASE WHEN f.weather_delay > 0 THEN 1 ELSE 0 END) AS weather_delayed,
  ROUND(100.0 * SUM(CASE WHEN f.weather_delay > 0 THEN 1 ELSE 0 END) / COUNT(*), 2) AS weather_delay_rate_pct,
  ROUND(AVG(CASE WHEN f.weather_delay > 0 THEN f.weather_delay END), 2) AS avg_weather_delay_min,
  FLOOR((
    SUM(CAST(ROUND(COALESCE(f.weather_delay, 0) * 10) AS BIGINT)) + 5
  ) / 10) AS total_weather_minutes
FROM flights AS f
WHERE
  f.cancelled = 0 AND f.flight_date >= '2021-08-01' AND f.flight_date < '2025-01-01'
GROUP BY
  f.month
ORDER BY
  f.month

Representative DataFrame

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

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

    return (
        flights.with_columns(
            (col("weather_delay") > lit(0)).cast(int).alias("_weather"),
            ctx.when(col("weather_delay") > lit(0))
            .then(col("weather_delay"))
            .otherwise(lit(None))
            .alias("_weather_pos"),
            _round_half_away(ctx, col("weather_delay").fill_null(lit(0)) * lit(10), 0)
            .cast(int)
            .alias("_weather_tenths"),
        )
        .group_by("month")
        .agg(
            col("flight_id").count().alias("total_flights"),
            col("_weather").sum().alias("weather_delayed"),
            col("_weather_pos").mean().alias("_avg_weather"),
            col("_weather_tenths").sum().alias("_tenths_sum"),
        )
        .with_columns(
            _round_half_away(ctx, lit(100.0) * col("weather_delayed") / col("total_flights"), 2).alias(
                "weather_delay_rate_pct"
            ),
            _round_half_away(ctx, col("_avg_weather"), 2).alias("avg_weather_delay_min"),
            ((col("_tenths_sum") + lit(5)) / lit(10)).floor().alias("total_weather_minutes"),
        )
        .select(
            "month",
            "total_flights",
            "weather_delayed",
            "weather_delay_rate_pct",
            "avg_weather_delay_min",
            "total_weather_minutes",
        )
        .sort("month")
    )

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()['weather-impact'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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