TPC-DS Q25 — Store/Catalog Sales Item Analysis

TPC-DS benchmark · all TPC-DS queries

Compares store and catalog sales for items in specific quarters

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  i_item_id,
  i_item_desc,
  s_store_id,
  s_store_name,
  SUM(ss_net_profit) AS store_sales_profit,
  SUM(sr_net_loss) AS store_returns_loss,
  SUM(cs_net_profit) AS catalog_sales_profit
FROM store_sales, store_returns, catalog_sales, date_dim AS d1, date_dim AS d2, date_dim AS d3, store, item
WHERE
  d1.d_moy = 4
  AND d1.d_year = 2000
  AND d1.d_date_sk = ss_sold_date_sk
  AND i_item_sk = ss_item_sk
  AND s_store_sk = ss_store_sk
  AND ss_customer_sk = sr_customer_sk
  AND ss_item_sk = sr_item_sk
  AND ss_ticket_number = sr_ticket_number
  AND sr_returned_date_sk = d2.d_date_sk
  AND d2.d_moy BETWEEN 4 AND 10
  AND d2.d_year = 2000
  AND sr_customer_sk = cs_bill_customer_sk
  AND sr_item_sk = cs_item_sk
  AND cs_sold_date_sk = d3.d_date_sk
  AND d3.d_moy BETWEEN 4 AND 10
  AND d3.d_year = 2000
GROUP BY
  i_item_id,
  i_item_desc,
  s_store_id,
  s_store_name
ORDER BY
  i_item_id,
  i_item_desc,
  s_store_id,
  s_store_name
LIMIT 100

Representative DataFrame

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

def q25_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(25)
    year = params.get("year", 2000)
    agg = _q25_aggregate(params.get("agg", "sum"))
    month = _Q25_FIRST_MONTH

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

    d1 = date_dim.filter((col("d_year") == lit(year)) & (col("d_moy") == lit(month)))
    d2 = date_dim.filter((col("d_year") == lit(year)) & (col("d_moy") >= lit(month)) & (col("d_moy") <= lit(month + 6)))
    d3 = date_dim.filter((col("d_year") == lit(year)) & (col("d_moy") >= lit(month)) & (col("d_moy") <= lit(month + 6)))

    joined_returns = (
        store_sales.join(item, left_on="ss_item_sk", right_on="i_item_sk")
        .join(store, left_on="ss_store_sk", right_on="s_store_sk")
        .join(d1, left_on="ss_sold_date_sk", right_on="d_date_sk")
        .join(
            store_returns,
            left_on=["ss_customer_sk", "ss_item_sk", "ss_ticket_number"],
            right_on=["sr_customer_sk", "sr_item_sk", "sr_ticket_number"],
        )
        .join(d2.select("d_date_sk"), left_on="sr_returned_date_sk", right_on="d_date_sk")
    )
    return _sort_null_largest_expression(
        ctx,
        joined_returns.join(
            catalog_sales,
            left_on=["ss_customer_sk", "ss_item_sk"],
            right_on=["cs_bill_customer_sk", "cs_item_sk"],
        )
        .join(d3.select("d_date_sk"), left_on="cs_sold_date_sk", right_on="d_date_sk")
        .group_by("i_item_id", "i_item_desc", "s_store_id", "s_store_name")
        .agg(
            *(
                (_sum_or_null_expression(ctx, col(source)) if agg == "sum" else getattr(col(source), agg)()).alias(
                    alias
                )
                for source, alias in (
                    ("ss_net_profit", "store_sales_profit"),
                    ("sr_net_loss", "store_returns_loss"),
                    ("cs_net_profit", "catalog_sales_profit"),
                )
            ),
        ),
        ["i_item_id", "i_item_desc", "s_store_id", "s_store_name"],
    ).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['25'])

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

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

Source

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