SSB — Revenue by City/Year (Nation)

SSB benchmark · all SSB queries

Revenue by city and year filtered by nation (drill-down from region)

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  c_city,
  s_city,
  d_year,
  SUM(lo_revenue) AS revenue
FROM customer, lineorder, supplier, date
WHERE
  lo_custkey = c_custkey
  AND lo_suppkey = s_suppkey
  AND lo_orderdate = d_datekey
  AND c_nation = 'UNITED STATES'
  AND s_nation = 'UNITED STATES'
  AND d_year >= 1992
  AND d_year <= 1997
GROUP BY
  c_city,
  s_city,
  d_year
ORDER BY
  d_year ASC,
  revenue DESC

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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