TPC-DS Q99 — Catalog Sales Delivery Analysis

TPC-DS benchmark · all TPC-DS queries

Analyzes catalog sales delivery times by warehouse, ship mode, and call center

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  SUBSTRING(w_warehouse_name FROM 1 FOR 20),
  sm_type,
  cc_name,
  SUM(CASE WHEN (
    cs_ship_date_sk - cs_sold_date_sk <= 30
  ) THEN 1 ELSE 0 END) AS "30 days",
  SUM(
    CASE
      WHEN (
        cs_ship_date_sk - cs_sold_date_sk > 30
      )
      AND (
        cs_ship_date_sk - cs_sold_date_sk <= 60
      )
      THEN 1
      ELSE 0
    END
  ) AS "31-60 days",
  SUM(
    CASE
      WHEN (
        cs_ship_date_sk - cs_sold_date_sk > 60
      )
      AND (
        cs_ship_date_sk - cs_sold_date_sk <= 90
      )
      THEN 1
      ELSE 0
    END
  ) AS "61-90 days",
  SUM(
    CASE
      WHEN (
        cs_ship_date_sk - cs_sold_date_sk > 90
      )
      AND (
        cs_ship_date_sk - cs_sold_date_sk <= 120
      )
      THEN 1
      ELSE 0
    END
  ) AS "91-120 days",
  SUM(CASE WHEN (
    cs_ship_date_sk - cs_sold_date_sk > 120
  ) THEN 1 ELSE 0 END) AS ">120 days"
FROM catalog_sales, warehouse, ship_mode, call_center, date_dim
WHERE
  d_month_seq BETWEEN 1212 AND 1212 + 11
  AND cs_ship_date_sk = d_date_sk
  AND cs_warehouse_sk = w_warehouse_sk
  AND cs_ship_mode_sk = sm_ship_mode_sk
  AND cs_call_center_sk = cc_call_center_sk
GROUP BY
  SUBSTRING(w_warehouse_name FROM 1 FOR 20),
  sm_type,
  cc_name
ORDER BY
  SUBSTRING(w_warehouse_name FROM 1 FOR 20),
  sm_type,
  cc_name
LIMIT 100

Representative DataFrame

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

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

    catalog_sales, warehouse, ship_mode, call_center, date_dim = _tables(
        ctx, "catalog_sales", "warehouse", "ship_mode", "call_center", "date_dim"
    )
    col = ctx.col

    return (
        catalog_sales.join(date_dim, left_on="cs_ship_date_sk", right_on="d_date_sk")
        .join(warehouse, left_on="cs_warehouse_sk", right_on="w_warehouse_sk")
        .join(ship_mode, left_on="cs_ship_mode_sk", right_on="sm_ship_mode_sk")
        .join(call_center, left_on="cs_call_center_sk", right_on="cc_call_center_sk")
        .filter(col("d_month_seq").is_between(dms, dms + 11))
        .with_columns((col("cs_ship_date_sk") - col("cs_sold_date_sk")).alias("delivery_days"))
        .group_by(
            col("w_warehouse_name").str.slice(0, 20).alias("warehouse_name"),
            col("sm_type"),
            col("cc_name"),
        )
        .agg(
            ctx.when(col("delivery_days") <= 30).then(1).otherwise(0).sum().alias("30_days"),
            ctx.when((col("delivery_days") > 30) & (col("delivery_days") <= 60))
            .then(1)
            .otherwise(0)
            .sum()
            .alias("31_60_days"),
            ctx.when((col("delivery_days") > 60) & (col("delivery_days") <= 90))
            .then(1)
            .otherwise(0)
            .sum()
            .alias("61_90_days"),
            ctx.when((col("delivery_days") > 90) & (col("delivery_days") <= 120))
            .then(1)
            .otherwise(0)
            .sum()
            .alias("91_120_days"),
            ctx.when(col("delivery_days") > 120).then(1).otherwise(0).sum().alias("gt_120_days"),
        )
        .sort("warehouse_name", "sm_type", "cc_name", nulls_last=True)
        .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['99'])

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

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

Source

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