SSB — Revenue by Year/Brand (Brand Range)

SSB benchmark · all SSB queries

Revenue by year and brand filtered by brand range and supplier region

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  d_year,
  p_brand1,
  SUM(lo_revenue) AS revenue
FROM lineorder, date, part, supplier
WHERE
  lo_orderdate = d_datekey
  AND lo_partkey = p_partkey
  AND lo_suppkey = s_suppkey
  AND p_brand1 BETWEEN 'MFGR#2221' AND 'MFGR#2228'
  AND s_region = 'AMERICA'
GROUP BY
  d_year,
  p_brand1
ORDER BY
  d_year,
  p_brand1

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['Q2.2'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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