TPC-DS Q21 — Inventory Before/After Date

TPC-DS benchmark · all TPC-DS queries

Compares inventory quantities before and after a specific date by warehouse

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  *
FROM (
  SELECT
    w_warehouse_name,
    i_item_id,
    SUM(
      CASE
        WHEN (
          CAST(d_date AS DATE) < CAST('1998-04-08' AS DATE)
        )
        THEN inv_quantity_on_hand
        ELSE 0
      END
    ) AS inv_before,
    SUM(
      CASE
        WHEN (
          CAST(d_date AS DATE) >= CAST('1998-04-08' AS DATE)
        )
        THEN inv_quantity_on_hand
        ELSE 0
      END
    ) AS inv_after
  FROM inventory, warehouse, item, date_dim
  WHERE
    i_current_price BETWEEN 0.99 AND 1.49
    AND i_item_sk = inv_item_sk
    AND inv_warehouse_sk = w_warehouse_sk
    AND inv_date_sk = d_date_sk
    AND d_date BETWEEN (
      CAST('1998-04-08' AS DATE) - INTERVAL '30 DAY'
    ) AND (
      CAST('1998-04-08' AS DATE) + INTERVAL '30 DAY'
    )
  GROUP BY
    w_warehouse_name,
    i_item_id
) AS x
WHERE
  (
    CASE WHEN inv_before > 0 THEN inv_after / inv_before ELSE NULL END
  ) BETWEEN 2.0 / 3.0 AND 3.0 / 2.0
ORDER BY
  w_warehouse_name,
  i_item_id
LIMIT 100

Representative DataFrame

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

def q21_expression_impl(ctx: DataFrameContext) -> Any:
    from datetime import timedelta

    params = get_parameters(21)
    price_min, price_max = 0.99, 1.49
    sales_date_default = params.get("sales_date", "1998-04-08")

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

    col = ctx.col
    lit = ctx.lit

    sales_date = (
        datetime.strptime(sales_date_default, "%Y-%m-%d").date()
        if isinstance(sales_date_default, str)
        else sales_date_default
    )
    date_start = sales_date - timedelta(days=30)
    date_end = sales_date + timedelta(days=30)

    item_filtered = item.filter((col("i_current_price") >= lit(price_min)) & (col("i_current_price") <= lit(price_max)))

    date_filtered = date_dim.filter((col("d_date") >= lit(date_start)) & (col("d_date") <= lit(date_end)))

    inv_data = (
        inventory.join(item_filtered, left_on="inv_item_sk", right_on="i_item_sk")
        .join(warehouse, left_on="inv_warehouse_sk", right_on="w_warehouse_sk")
        .join(date_filtered, left_on="inv_date_sk", right_on="d_date_sk")
    )

    inv_data = inv_data.with_columns(
        [
            ctx.when(col("d_date") < lit(sales_date))
            .then(col("inv_quantity_on_hand"))
            .otherwise(lit(0))
            .alias("inv_before"),
            ctx.when(col("d_date") >= lit(sales_date))
            .then(col("inv_quantity_on_hand"))
            .otherwise(lit(0))
            .alias("inv_after"),
        ]
    )

    grouped = inv_data.group_by(["w_warehouse_name", "i_item_id"]).agg(
        [
            ctx.sum("inv_before").alias("inv_before"),
            ctx.sum("inv_after").alias("inv_after"),
        ]
    )

    return (
        grouped.filter(
            (col("inv_before") > lit(0))
            & ((col("inv_after") / col("inv_before")) >= lit(2.0 / 3.0))
            & ((col("inv_after") / col("inv_before")) <= lit(3.0 / 2.0))
        )
        .sort(["w_warehouse_name", "i_item_id"], nulls_last=True)
        .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['21'])

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

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

Source

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