SSB — Revenue by Week

SSB benchmark · all SSB queries

Revenue drill-down from year-month to week with quantity range filter

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  SUM(lo_extendedprice * lo_discount) AS revenue
FROM lineorder, date
WHERE
  lo_orderdate = d_datekey
  AND d_weeknuminyear = 6
  AND d_year = 1993
  AND lo_discount BETWEEN 1 AND 3
  AND lo_quantity BETWEEN 26 AND 35

Representative DataFrame

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

def impl(ctx: DataFrameContext) -> Any:
        col = ctx.col
        filtered = _expr_join(ctx, spec["joins"]).filter(_expr_filter(ctx, spec["filters"], _query_params(spec["id"])))
        if spec["metric"] == "scalar_revenue":
            return filtered.select((col("lo_extendedprice") * col("lo_discount")).sum().alias("revenue"))
        metric = (
            (col("lo_revenue") - col("lo_supplycost")).sum().alias("profit")
            if spec["metric"] == "profit"
            else col("lo_revenue").sum().alias("revenue")
        )
        return (
            filtered.group_by(*_items(spec["group"]))
            .agg(metric)
            .sort(**_sort_kwargs(spec["sort"], spec["desc"], pandas=False))
        )

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 SSB

bench = SSB(scale_factor=0.01)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries['Q1.3'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('ssb', 'Q1.3', family="expression").source)  # or family="pandas"

Source

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