SSB — Profit by Year/Nation/Category

SSB benchmark · all SSB queries

Profit by year, supplier nation, and part category with year and manufacturer filters

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  d_year,
  s_nation,
  p_category,
  SUM(lo_revenue - lo_supplycost) AS profit
FROM date, customer, supplier, part, lineorder
WHERE
  lo_custkey = c_custkey
  AND lo_suppkey = s_suppkey
  AND lo_partkey = p_partkey
  AND lo_orderdate = d_datekey
  AND c_region = 'AMERICA'
  AND s_region = 'AMERICA'
  AND (
    d_year = 1997 OR d_year = 1998
  )
  AND (
    p_mfgr = 'MFGR#1' OR p_mfgr = 'MFGR#2'
  )
GROUP BY
  d_year,
  s_nation,
  p_category
ORDER BY
  d_year,
  s_nation,
  p_category

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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