TPC-DS Q17 — Store/Catalog Sales-Returns Statistics

TPC-DS benchmark · all TPC-DS queries

Store sales/returns joined with catalog sales with quantity statistics

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  i_item_id,
  i_item_desc,
  s_state,
  COUNT(ss_quantity) AS store_sales_quantitycount,
  AVG(ss_quantity) AS store_sales_quantityave,
  STDDEV_SAMP(ss_quantity) AS store_sales_quantitystdev,
  STDDEV_SAMP(ss_quantity) / AVG(ss_quantity) AS store_sales_quantitycov,
  COUNT(sr_return_quantity) AS store_returns_quantitycount,
  AVG(sr_return_quantity) AS store_returns_quantityave,
  STDDEV_SAMP(sr_return_quantity) AS store_returns_quantitystdev,
  STDDEV_SAMP(sr_return_quantity) / AVG(sr_return_quantity) AS store_returns_quantitycov,
  COUNT(cs_quantity) AS catalog_sales_quantitycount,
  AVG(cs_quantity) AS catalog_sales_quantityave,
  STDDEV_SAMP(cs_quantity) AS catalog_sales_quantitystdev,
  STDDEV_SAMP(cs_quantity) / AVG(cs_quantity) AS catalog_sales_quantitycov
FROM store_sales, store_returns, catalog_sales, date_dim AS d1, date_dim AS d2, date_dim AS d3, store, item
WHERE
  d1.d_quarter_name = '1998Q1'
  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_quarter_name IN ('1998Q1', '1998Q2', '1998Q3')
  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_quarter_name IN ('1998Q1', '1998Q2', '1998Q3')
GROUP BY
  i_item_id,
  i_item_desc,
  s_state
ORDER BY
  i_item_id,
  i_item_desc,
  s_state
LIMIT 100

Representative DataFrame

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

def q17_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(17)
    year = params.get("year", 1998)
    quarter = params.get("quarter", 1)

    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

    quarter_name = f"{year}Q{quarter}"
    d1 = date_dim.filter(col("d_quarter_name") == lit(quarter_name))

    quarter_names_ret = [f"{year}Q{q}" for q in range(quarter, min(quarter + 3, 5))]
    d2 = date_dim.filter(col("d_quarter_name").is_in(quarter_names_ret))

    d3 = date_dim.filter(col("d_quarter_name").is_in(quarter_names_ret))

    ss_joined = (
        store_sales.join(d1.select("d_date_sk"), left_on="ss_sold_date_sk", right_on="d_date_sk")
        .join(item, left_on="ss_item_sk", right_on="i_item_sk")
        .join(store, left_on="ss_store_sk", right_on="s_store_sk")
    )

    sr_joined = store_returns.join(d2.select("d_date_sk"), left_on="sr_returned_date_sk", right_on="d_date_sk")

    ss_sr = ss_joined.join(
        sr_joined,
        left_on=["ss_customer_sk", "ss_item_sk", "ss_ticket_number"],
        right_on=["sr_customer_sk", "sr_item_sk", "sr_ticket_number"],
    )

    cs_joined = catalog_sales.join(d3.select("d_date_sk"), left_on="cs_sold_date_sk", right_on="d_date_sk")

    result = ss_sr.join(
        cs_joined,
        left_on=["ss_customer_sk", "ss_item_sk"],
        right_on=["cs_bill_customer_sk", "cs_item_sk"],
    )

    return _sort_null_largest_expression(
        ctx,
        result.group_by(["i_item_id", "i_item_desc", "s_state"])
        .agg(
            [
                ctx.count("ss_quantity").alias("store_sales_quantitycount"),
                col("ss_quantity").mean().alias("store_sales_quantityave"),
                col("ss_quantity").std().alias("store_sales_quantitystdev"),
                ctx.count("sr_return_quantity").alias("store_returns_quantitycount"),
                col("sr_return_quantity").mean().alias("store_returns_quantityave"),
                col("sr_return_quantity").std().alias("store_returns_quantitystdev"),
                ctx.count("cs_quantity").alias("catalog_sales_quantitycount"),
                col("cs_quantity").mean().alias("catalog_sales_quantityave"),
                col("cs_quantity").std().alias("catalog_sales_quantitystdev"),
            ]
        )
        .with_columns(
            [
                (col("store_sales_quantitystdev") / col("store_sales_quantityave")).alias("store_sales_quantitycov"),
                (col("store_returns_quantitystdev") / col("store_returns_quantityave")).alias(
                    "store_returns_quantitycov"
                ),
                (col("catalog_sales_quantitystdev") / col("catalog_sales_quantityave")).alias(
                    "catalog_sales_quantitycov"
                ),
            ]
        )
        .select(
            [
                "i_item_id",
                "i_item_desc",
                "s_state",
                "store_sales_quantitycount",
                "store_sales_quantityave",
                "store_sales_quantitystdev",
                "store_sales_quantitycov",
                "store_returns_quantitycount",
                "store_returns_quantityave",
                "store_returns_quantitystdev",
                "store_returns_quantitycov",
                "catalog_sales_quantitycount",
                "catalog_sales_quantityave",
                "catalog_sales_quantitystdev",
                "catalog_sales_quantitycov",
            ]
        ),
        ["i_item_id", "i_item_desc", "s_state"],
    ).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['17'])

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

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

Source

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