TPC-DS Q76 — Three-Channel Sales with NULL Column Filter

TPC-DS benchmark · all TPC-DS queries

Sales analysis across channels where specific columns are NULL

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  channel,
  col_name,
  d_year,
  d_qoy,
  i_category,
  COUNT(*) AS sales_cnt,
  SUM(ext_sales_price) AS sales_amt
FROM (
  SELECT
    'store' AS channel,
    'ss_addr_sk' AS col_name,
    d_year,
    d_qoy,
    i_category,
    ss_ext_sales_price AS ext_sales_price
  FROM store_sales, item, date_dim
  WHERE
    ss_addr_sk IS NULL AND ss_sold_date_sk = d_date_sk AND ss_item_sk = i_item_sk
  UNION ALL
  SELECT
    'web' AS channel,
    'ws_web_page_sk' AS col_name,
    d_year,
    d_qoy,
    i_category,
    ws_ext_sales_price AS ext_sales_price
  FROM web_sales, item, date_dim
  WHERE
    ws_web_page_sk IS NULL AND ws_sold_date_sk = d_date_sk AND ws_item_sk = i_item_sk
  UNION ALL
  SELECT
    'catalog' AS channel,
    'cs_warehouse_sk' AS col_name,
    d_year,
    d_qoy,
    i_category,
    cs_ext_sales_price AS ext_sales_price
  FROM catalog_sales, item, date_dim
  WHERE
    cs_warehouse_sk IS NULL AND cs_sold_date_sk = d_date_sk AND cs_item_sk = i_item_sk
) AS foo
GROUP BY
  channel,
  col_name,
  d_year,
  d_qoy,
  i_category
ORDER BY
  channel,
  col_name,
  d_year,
  d_qoy,
  i_category
LIMIT 100

Representative DataFrame

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

def q76_expression_impl(ctx: DataFrameContext) -> Any:
    col = ctx.col
    lit = ctx.lit

    params = get_parameters(76)
    null_col_ss = params.get("null_col_ss", "ss_customer_sk")
    null_col_ws = params.get("null_col_ws", "ws_bill_customer_sk")
    null_col_cs = params.get("null_col_cs", "cs_bill_customer_sk")

    date_dim, item = _tables(ctx, "date_dim", "item")

    def channel(name: str, table: str, null_col: str, date_key: str, item_key: str, value_col: str) -> Any:
        return (
            ctx.get_table(table)
            .filter(col(null_col).is_null())
            .join(date_dim, left_on=date_key, right_on="d_date_sk", how="inner")
            .join(item, left_on=item_key, right_on="i_item_sk", how="inner")
            .select(
                [
                    lit(name).alias("channel"),
                    lit(null_col).alias("col_name"),
                    col("d_year"),
                    col("d_qoy"),
                    col("i_category"),
                    col(value_col).alias("ext_sales_price"),
                ]
            )
        )

    combined = ctx.concat(
        [
            channel("store", "store_sales", null_col_ss, "ss_sold_date_sk", "ss_item_sk", "ss_ext_sales_price"),
            channel("web", "web_sales", null_col_ws, "ws_sold_date_sk", "ws_item_sk", "ws_ext_sales_price"),
            channel("catalog", "catalog_sales", null_col_cs, "cs_sold_date_sk", "cs_item_sk", "cs_ext_sales_price"),
        ]
    )

    grouped = combined.group_by(["channel", "col_name", "d_year", "d_qoy", "i_category"]).agg(
        [
            ctx.count().alias("sales_cnt"),
            ctx.when(col("ext_sales_price").count() > lit(0))
            .then(col("ext_sales_price").sum())
            .otherwise(lit(None))
            .alias("sales_amt"),
        ]
    )
    return _sort_null_largest_expression(
        ctx, grouped, ["channel", "col_name", "d_year", "d_qoy", "i_category"], [False] * 5
    ).head(100)

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 TPCDS

bench = TPCDS(scale_factor=1.0)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries['76'])

Parameter substitution for this benchmark is seed-driven; pass a seed for a specific stream’s parameters:

print(bench.get_query(76, scale_factor=1.0, seed=42, dialect="datafusion"))

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('tpcds', '76', family="expression").source)  # or family="pandas"

Source

Query text: _sources/tpc-ds/query_templates/query76.tpl