CoffeeShop — Lines per Order

CoffeeShop benchmark · all CoffeeShop queries

Average lines and items per order with NULLIF protection

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  COUNT(*) * 1.0 / NULLIF(COUNT(DISTINCT ol.order_id), 0) AS avg_lines_per_order,
  SUM(ol.quantity) / NULLIF(COUNT(DISTINCT ol.order_id), 0) AS avg_items_per_order
FROM order_lines AS ol
WHERE
  ol.order_date BETWEEN CAST('2023-01-01' AS DATE) AND CAST('2024-12-31' AS DATE)

Representative DataFrame

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

def qc1_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters("QC1")
    start_date = params.get("start_date", "2023-01-01")
    end_date = params.get("end_date", "2024-12-31")

    ol = ctx.get_table("order_lines")
    col = ctx.col
    lit = ctx.lit

    filtered = ol.filter(
        (col("order_date") >= lit(_parse_date(start_date))) & (col("order_date") <= lit(_parse_date(end_date)))
    )

    return filtered.select(
        ctx.when(col("order_id").n_unique() != lit(0))
        .then(col("order_id").count().cast_float() / col("order_id").n_unique().cast_float())
        .otherwise(lit(None))
        .alias("avg_lines_per_order"),
        ctx.when(col("order_id").n_unique() != lit(0))
        .then(col("quantity").sum().cast_float() / col("order_id").n_unique().cast_float())
        .otherwise(lit(None))
        .alias("avg_items_per_order"),
    )

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 CoffeeShop

bench = CoffeeShop(scale_factor=0.001)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries['QC1'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('coffeeshop', 'QC1', family="expression").source)  # or family="pandas"

Source

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