TPC-DS Q23 — Frequent Items Best Customers

TPC-DS benchmark · all TPC-DS queries

Sum of catalog+web sales for frequent items by best customers

Representative SQL

Rendered for DataFusion with default parameters.

WITH frequent_ss_items AS (
  SELECT
    SUBSTRING(i_item_desc FROM 1 FOR 30) AS itemdesc,
    i_item_sk AS item_sk,
    d_date AS solddate,
    COUNT(*) AS cnt
  FROM store_sales, date_dim, item
  WHERE
    ss_sold_date_sk = d_date_sk
    AND ss_item_sk = i_item_sk
    AND d_year IN (1999, 1999 + 1, 1999 + 2, 1999 + 3)
  GROUP BY
    SUBSTRING(i_item_desc FROM 1 FOR 30),
    i_item_sk,
    d_date
  HAVING
    COUNT(*) > 4
), max_store_sales AS (
  SELECT
    MAX(csales) AS tpcds_cmax
  FROM (
    SELECT
      c_customer_sk,
      SUM(ss_quantity * ss_sales_price) AS csales
    FROM store_sales, customer, date_dim
    WHERE
      ss_customer_sk = c_customer_sk
      AND ss_sold_date_sk = d_date_sk
      AND d_year IN (1999, 1999 + 1, 1999 + 2, 1999 + 3)
    GROUP BY
      c_customer_sk
  )
), best_ss_customer AS (
  SELECT
    c_customer_sk,
    SUM(ss_quantity * ss_sales_price) AS ssales
  FROM store_sales, customer
  WHERE
    ss_customer_sk = c_customer_sk
  GROUP BY
    c_customer_sk
  HAVING
    SUM(ss_quantity * ss_sales_price) > (
      95 / 100.0
    ) * (
      SELECT
        *
      FROM max_store_sales
    )
)
SELECT
  SUM(sales)
FROM (
  SELECT
    cs_quantity * cs_list_price AS sales
  FROM catalog_sales, date_dim
  WHERE
    d_year = 1999
    AND d_moy = 1
    AND cs_sold_date_sk = d_date_sk
    AND cs_item_sk IN (
      SELECT
        item_sk
      FROM frequent_ss_items
    )
    AND cs_bill_customer_sk IN (
      SELECT
        c_customer_sk
      FROM best_ss_customer
    )
  UNION ALL
  SELECT
    ws_quantity * ws_list_price AS sales
  FROM web_sales, date_dim
  WHERE
    d_year = 1999
    AND d_moy = 1
    AND ws_sold_date_sk = d_date_sk
    AND ws_item_sk IN (
      SELECT
        item_sk
      FROM frequent_ss_items
    )
    AND ws_bill_customer_sk IN (
      SELECT
        c_customer_sk
      FROM best_ss_customer
    )
)
LIMIT 100

Representative DataFrame

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

def q23_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(23)
    year = params.get("year", 1999)
    month = params.get("month", 1)
    top_percent = params.get("top_percent", 95)

    store_sales, catalog_sales, web_sales, customer, item, date_dim = _tables(
        ctx, "store_sales", "catalog_sales", "web_sales", "customer", "item", "date_dim"
    )

    col = ctx.col
    lit = ctx.lit

    frequent_items, best_customers = _q23_frequent_items_and_best_customers_expression(
        ctx, year, top_percent, store_sales, customer, item, date_dim
    )

    date_target = date_dim.filter((col("d_year") == lit(year)) & (col("d_moy") == lit(month)))

    cs_sales = (
        catalog_sales.join(date_target, left_on="cs_sold_date_sk", right_on="d_date_sk")
        .join(frequent_items, left_on="cs_item_sk", right_on="i_item_sk", how="semi")
        .join(best_customers, left_on="cs_bill_customer_sk", right_on="c_customer_sk", how="semi")
        .select((col("cs_quantity") * col("cs_list_price")).alias("sales"))
    )

    ws_sales = (
        web_sales.join(date_target, left_on="ws_sold_date_sk", right_on="d_date_sk")
        .join(frequent_items, left_on="ws_item_sk", right_on="i_item_sk", how="semi")
        .join(best_customers, left_on="ws_bill_customer_sk", right_on="c_customer_sk", how="semi")
        .select((col("ws_quantity") * col("ws_list_price")).alias("sales"))
    )

    all_sales = ctx.concat([cs_sales, ws_sales])
    tallied = all_sales.select(
        ctx.sum("sales").alias("sum_sales"),
        col("sales").count().alias("n"),
    )
    return tallied.select(ctx.when(col("n") > lit(0)).then(col("sum_sales")).otherwise(lit(None)).alias("sum_sales"))

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

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

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

Source

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