TPC-DS Q97 — Store/Catalog Customer-Item Overlap

TPC-DS benchmark · all TPC-DS queries

Analyzes customer-item purchase overlap between store and catalog channels

Representative SQL

Rendered for DataFusion with default parameters.

WITH ssci AS (
  SELECT
    ss_customer_sk AS customer_sk,
    ss_item_sk AS item_sk
  FROM store_sales, date_dim
  WHERE
    ss_sold_date_sk = d_date_sk AND d_month_seq BETWEEN 1212 AND 1212 + 11
  GROUP BY
    ss_customer_sk,
    ss_item_sk
), csci AS (
  SELECT
    cs_bill_customer_sk AS customer_sk,
    cs_item_sk AS item_sk
  FROM catalog_sales, date_dim
  WHERE
    cs_sold_date_sk = d_date_sk AND d_month_seq BETWEEN 1212 AND 1212 + 11
  GROUP BY
    cs_bill_customer_sk,
    cs_item_sk
)
SELECT
  SUM(
    CASE
      WHEN ssci.customer_sk IS NOT NULL AND csci.customer_sk IS NULL
      THEN 1
      ELSE 0
    END
  ) AS store_only,
  SUM(
    CASE
      WHEN ssci.customer_sk IS NULL AND csci.customer_sk IS NOT NULL
      THEN 1
      ELSE 0
    END
  ) AS catalog_only,
  SUM(
    CASE
      WHEN ssci.customer_sk IS NOT NULL AND csci.customer_sk IS NOT NULL
      THEN 1
      ELSE 0
    END
  ) AS store_and_catalog
FROM ssci
FULL OUTER JOIN csci
  ON (
    ssci.customer_sk = csci.customer_sk AND ssci.item_sk = csci.item_sk
  )
LIMIT 100

Representative DataFrame

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

def q97_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(97)
    dms = params.get("dms", 1212)

    store_sales, catalog_sales, date_dim = _tables(ctx, "store_sales", "catalog_sales", "date_dim")
    col = ctx.col
    lit = ctx.lit

    date_filtered = date_dim.filter((col("d_month_seq") >= lit(dms)) & (col("d_month_seq") <= lit(dms + 11)))

    ssci = (
        store_sales.join(date_filtered, left_on="ss_sold_date_sk", right_on="d_date_sk")
        .group_by(["ss_customer_sk", "ss_item_sk"])
        .agg(lit(1).alias("_count"))
        .select(
            [
                col("ss_customer_sk").alias("ss_customer_sk"),
                col("ss_item_sk").alias("ss_item_sk"),
            ]
        )
    )

    csci = (
        catalog_sales.join(date_filtered, left_on="cs_sold_date_sk", right_on="d_date_sk")
        .group_by(["cs_bill_customer_sk", "cs_item_sk"])
        .agg(lit(1).alias("_count"))
        .select(
            [
                col("cs_bill_customer_sk").alias("cs_customer_sk"),
                col("cs_item_sk").alias("cs_item_sk"),
            ]
        )
    )

    joined = ssci.join(
        csci,
        left_on=["ss_customer_sk", "ss_item_sk"],
        right_on=["cs_customer_sk", "cs_item_sk"],
        how="full",
    )

    return joined.select(
        [
            ctx.when(col("ss_customer_sk").is_not_null() & col("cs_customer_sk").is_null())
            .then(lit(1))
            .otherwise(lit(0))
            .sum()
            .alias("store_only"),
            ctx.when(col("ss_customer_sk").is_null() & col("cs_customer_sk").is_not_null())
            .then(lit(1))
            .otherwise(lit(0))
            .sum()
            .alias("catalog_only"),
            ctx.when(col("ss_customer_sk").is_not_null() & col("cs_customer_sk").is_not_null())
            .then(lit(1))
            .otherwise(lit(0))
            .sum()
            .alias("store_and_catalog"),
        ]
    )

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['97'])

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

print(bench.get_query(97, 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', '97', family="expression").source)  # or family="pandas"

Source

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