CoffeeShop — Monthly Performance

CoffeeShop benchmark · all CoffeeShop queries

Monthly orders, items sold, revenue with NULLIF derived metrics

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  EXTRACT(YEAR FROM ol.order_date) AS year,
  EXTRACT(MONTH FROM ol.order_date) AS month,
  COUNT(DISTINCT ol.order_id) AS orders,
  SUM(ol.quantity) AS items_sold,
  SUM(ol.total_price) AS revenue,
  SUM(ol.total_price) / NULLIF(COUNT(DISTINCT ol.order_id), 0) AS avg_order_value,
  SUM(ol.quantity) / NULLIF(COUNT(DISTINCT ol.order_id), 0) AS avg_items_per_order
FROM order_lines AS ol
WHERE
  EXTRACT(YEAR FROM ol.order_date) = 2023
GROUP BY
  year,
  month
ORDER BY
  year,
  month

Representative DataFrame

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

def sa3_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters("SA3")
    year = params.get("year", 2023)

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

    return (
        ol.with_columns(
            col("order_date").dt.year().alias("year"),
            col("order_date").dt.month().alias("month"),
        )
        .filter(col("year") == lit(year))
        .group_by("year", "month")
        .agg(
            col("order_id").n_unique().alias("orders"),
            col("quantity").sum().alias("items_sold"),
            col("total_price").sum().alias("revenue"),
        )
        .with_columns(
            ctx.when(col("orders") != lit(0))
            .then(col("revenue") / col("orders"))
            .otherwise(lit(None))
            .alias("avg_order_value"),
            ctx.when(col("orders") != lit(0))
            .then(col("items_sold") / col("orders"))
            .otherwise(lit(None))
            .alias("avg_items_per_order"),
        )
        .sort(["year", "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 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['SA3'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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