Flight Data — Flight Patterns by Day of Week

Flight Data benchmark · all Flight Data queries

Flight volume and delay patterns by day of week

Representative SQL

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

SELECT
  f.day_of_week,
  CASE f.day_of_week
    WHEN 1
    THEN 'Monday'
    WHEN 2
    THEN 'Tuesday'
    WHEN 3
    THEN 'Wednesday'
    WHEN 4
    THEN 'Thursday'
    WHEN 5
    THEN 'Friday'
    WHEN 6
    THEN 'Saturday'
    WHEN 7
    THEN 'Sunday'
  END AS day_name,
  COUNT(*) AS total_flights,
  SUM(CASE WHEN f.cancelled = 1 THEN 1 ELSE 0 END) AS cancelled_count,
  ROUND(AVG(CASE WHEN f.cancelled = 0 THEN f.dep_delay END), 2) AS avg_dep_delay,
  ROUND(AVG(CASE WHEN f.cancelled = 0 THEN f.arr_delay END), 2) AS avg_arr_delay,
  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
FROM flights AS f
WHERE
  f.flight_date >= '2021-08-01' AND f.flight_date < '2025-01-01'
GROUP BY
  f.day_of_week
ORDER BY
  f.day_of_week

Representative DataFrame

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

def day_of_week_expression_impl(ctx: DataFrameContext) -> Any:
    flights, col, lit = _date_window(ctx)

    return (
        flights.with_columns(
            ctx.when(col("day_of_week") == lit(1))
            .then(lit("Monday"))
            .when(col("day_of_week") == lit(2))
            .then(lit("Tuesday"))
            .when(col("day_of_week") == lit(3))
            .then(lit("Wednesday"))
            .when(col("day_of_week") == lit(4))
            .then(lit("Thursday"))
            .when(col("day_of_week") == lit(5))
            .then(lit("Friday"))
            .when(col("day_of_week") == lit(6))
            .then(lit("Saturday"))
            .otherwise(lit("Sunday"))
            .alias("day_name"),
            (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"),
            ctx.when(col("cancelled") == lit(0)).then(col("dep_delay")).otherwise(lit(None)).alias("_dep_nc"),
            ctx.when(col("cancelled") == lit(0)).then(col("arr_delay")).otherwise(lit(None)).alias("_arr_nc"),
        )
        .group_by("day_of_week", "day_name")
        .agg(
            col("flight_id").count().alias("total_flights"),
            col("_cancelled").sum().alias("cancelled_count"),
            col("_dep_nc").mean().alias("_avg_dep"),
            col("_arr_nc").mean().alias("_avg_arr"),
            col("_ontime").sum().alias("_ontime_sum"),
            col("_operated").sum().alias("_non_cancelled"),
        )
        .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, col("_avg_dep"), 2).alias("avg_dep_delay"),
            _round_half_away(ctx, col("_avg_arr"), 2).alias("avg_arr_delay"),
            _round_half_away(ctx, lit(100.0) * col("_ontime_sum") / col("_non_cancelled_nz"), 2).alias("ontime_pct"),
        )
        .select(
            "day_of_week",
            "day_name",
            "total_flights",
            "cancelled_count",
            "avg_dep_delay",
            "avg_arr_delay",
            "ontime_pct",
        )
        .sort("day_of_week")
    )

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()['day-of-week'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('flightdata', 'day-of-week', family="expression").source)  # or family="pandas"

Source

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