TPC-DS Q72 — Catalog Sales Inventory

TPC-DS benchmark · all TPC-DS queries

Reports catalog sales vs inventory analysis

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  i_item_desc,
  w_warehouse_name,
  d1.d_week_seq,
  SUM(CASE WHEN p_promo_sk IS NULL THEN 1 ELSE 0 END) AS no_promo,
  SUM(CASE WHEN p_promo_sk IS NOT NULL THEN 1 ELSE 0 END) AS promo,
  COUNT(*) AS total_cnt
FROM catalog_sales
JOIN inventory
  ON (
    cs_item_sk = inv_item_sk
  )
JOIN warehouse
  ON (
    w_warehouse_sk = inv_warehouse_sk
  )
JOIN item
  ON (
    i_item_sk = cs_item_sk
  )
JOIN customer_demographics
  ON (
    cs_bill_cdemo_sk = cd_demo_sk
  )
JOIN household_demographics
  ON (
    cs_bill_hdemo_sk = hd_demo_sk
  )
JOIN date_dim AS d1
  ON (
    cs_sold_date_sk = d1.d_date_sk
  )
JOIN date_dim AS d2
  ON (
    inv_date_sk = d2.d_date_sk
  )
JOIN date_dim AS d3
  ON (
    cs_ship_date_sk = d3.d_date_sk
  )
LEFT OUTER JOIN promotion
  ON (
    cs_promo_sk = p_promo_sk
  )
LEFT OUTER JOIN catalog_returns
  ON (
    cr_item_sk = cs_item_sk AND cr_order_number = cs_order_number
  )
WHERE
  d1.d_week_seq = d2.d_week_seq
  AND inv_quantity_on_hand < cs_quantity
  AND d3.d_date > d1.d_date + 5
  AND hd_buy_potential = '1001-5000'
  AND d1.d_year = 2001
  AND cd_marital_status = 'M'
GROUP BY
  i_item_desc,
  w_warehouse_name,
  d1.d_week_seq
ORDER BY
  total_cnt DESC,
  i_item_desc,
  w_warehouse_name,
  d1.d_week_seq
LIMIT 100

Representative DataFrame

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

def q72_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(72)
    year = params.get("year", 2001)
    buy_potential = params.get("buy_potential", "1001-5000")
    marital_status = params.get("marital_status", "M")

    catalog_sales, inventory, date_dim, item, warehouse, customer_demographics, household_demographics, promotion = (
        _tables(
            ctx,
            "catalog_sales",
            "inventory",
            "date_dim",
            "item",
            "warehouse",
            "customer_demographics",
            "household_demographics",
            "promotion",
        )
    )
    col = ctx.col
    lit = ctx.lit

    cs_with_d1 = catalog_sales.join(
        date_dim.select(
            col("d_date_sk").alias("d1_date_sk"),
            col("d_week_seq").alias("cs_week_seq"),
            col("d_year").alias("d1_year"),
            col("d_date").alias("d1_date"),
        ),
        left_on="cs_sold_date_sk",
        right_on="d1_date_sk",
    ).filter(col("d1_year") == lit(year))

    inv_with_d2 = inventory.join(
        date_dim.select(
            col("d_date_sk").alias("d2_date_sk"),
            col("d_week_seq").alias("inv_week_seq"),
        ),
        left_on="inv_date_sk",
        right_on="d2_date_sk",
    )

    grouped = (
        cs_with_d1.join(
            inv_with_d2,
            left_on=["cs_item_sk", "cs_week_seq"],
            right_on=["inv_item_sk", "inv_week_seq"],
        )
        .join(
            date_dim.select(
                col("d_date_sk").alias("d3_date_sk"),
                col("d_date").alias("d3_date"),
            ),
            left_on="cs_ship_date_sk",
            right_on="d3_date_sk",
        )
        .join(warehouse, left_on="inv_warehouse_sk", right_on="w_warehouse_sk")
        .join(item, left_on="cs_item_sk", right_on="i_item_sk")
        .join(customer_demographics, left_on="cs_bill_cdemo_sk", right_on="cd_demo_sk")
        .join(household_demographics, left_on="cs_bill_hdemo_sk", right_on="hd_demo_sk")
        .join(promotion, left_on="cs_promo_sk", right_on="p_promo_sk", how="left")
        .filter(
            (col("inv_quantity_on_hand") < col("cs_quantity"))
            & (col("d3_date") > ctx.date_add(col("d1_date"), 5))
            & (col("hd_buy_potential") == lit(buy_potential))
            & (col("cd_marital_status") == lit(marital_status))
        )
        .group_by("i_item_desc", "w_warehouse_name", "cs_week_seq")
        .agg(
            ctx.when(col("p_promo_id").is_null()).then(1).otherwise(0).sum().alias("no_promo"),
            ctx.when(col("p_promo_id").is_not_null()).then(1).otherwise(0).sum().alias("promo"),
            ctx.len().alias("total_cnt"),
        )
    )
    return _sort_null_largest_expression(
        ctx, grouped, ["total_cnt", "i_item_desc", "w_warehouse_name", "cs_week_seq"], [True, False, False, False]
    ).limit(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['72'])

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

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

Source

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