TPC-DS Q57 — Catalog Sales Rolling Average

TPC-DS benchmark · all TPC-DS queries

Analyzes monthly catalog sales with rolling average deviation

Representative SQL

Rendered for DataFusion with default parameters.

WITH v1 AS (
  SELECT
    i_category,
    i_brand,
    cc_name,
    d_year,
    d_moy,
    SUM(cs_sales_price) AS sum_sales,
    AVG(SUM(cs_sales_price)) OVER (PARTITION BY i_category, i_brand, cc_name, d_year) AS avg_monthly_sales,
    RANK() OVER (PARTITION BY i_category, i_brand, cc_name ORDER BY d_year, d_moy) AS rn
  FROM item, catalog_sales, date_dim, call_center
  WHERE
    cs_item_sk = i_item_sk
    AND cs_sold_date_sk = d_date_sk
    AND cc_call_center_sk = cs_call_center_sk
    AND (
      d_year = 2000
      OR (
        d_year = 2000 - 1 AND d_moy = 12
      )
      OR (
        d_year = 2000 + 1 AND d_moy = 1
      )
    )
  GROUP BY
    i_category,
    i_brand,
    cc_name,
    d_year,
    d_moy
), v2 AS (
  SELECT
    v1.cc_name,
    v1.d_year,
    v1.d_moy,
    v1.avg_monthly_sales,
    v1.sum_sales,
    v1_lag.sum_sales AS psum,
    v1_lead.sum_sales AS nsum
  FROM v1, v1 AS v1_lag, v1 AS v1_lead
  WHERE
    v1.i_category = v1_lag.i_category
    AND v1.i_category = v1_lead.i_category
    AND v1.i_brand = v1_lag.i_brand
    AND v1.i_brand = v1_lead.i_brand
    AND v1.cc_name = v1_lag.cc_name
    AND v1.cc_name = v1_lead.cc_name
    AND v1.rn = v1_lag.rn + 1
    AND v1.rn = v1_lead.rn - 1
)
SELECT
  *
FROM v2
WHERE
  d_year = 2000
  AND avg_monthly_sales > 0
  AND CASE
    WHEN avg_monthly_sales > 0
    THEN ABS(sum_sales - avg_monthly_sales) / avg_monthly_sales
    ELSE NULL
  END > 0.1
ORDER BY
  sum_sales - avg_monthly_sales,
  nsum
LIMIT 100

Representative DataFrame

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

def q57_expression_impl(ctx: DataFrameContext) -> Any:
    return _rolling_average_expression_impl(
        ctx,
        query_id=57,
        sales_table="catalog_sales",
        date_sk_col="cs_sold_date_sk",
        item_sk_col="cs_item_sk",
        sales_price_col="cs_sales_price",
        channel_table="call_center",
        channel_join_key_left="cs_call_center_sk",
        channel_join_key_right="cc_call_center_sk",
        channel_cols=["cc_name"],
        select_one_default="v1.cc_name",
    )

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['57'])

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

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

Source

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