TPC-DS Q47 — Store Sales Rolling Average

TPC-DS benchmark · all TPC-DS queries

Analyzes monthly store sales with rolling average deviation

Representative SQL

Rendered for DataFusion with default parameters.

WITH v1 AS (
  SELECT
    i_category,
    i_brand,
    s_store_name,
    s_company_name,
    d_year,
    d_moy,
    SUM(ss_sales_price) AS sum_sales,
    AVG(SUM(ss_sales_price)) OVER (PARTITION BY i_category, i_brand, s_store_name, s_company_name, d_year) AS avg_monthly_sales,
    RANK() OVER (
      PARTITION BY i_category, i_brand, s_store_name, s_company_name
      ORDER BY d_year, d_moy
    ) AS rn
  FROM item, store_sales, date_dim, store
  WHERE
    ss_item_sk = i_item_sk
    AND ss_sold_date_sk = d_date_sk
    AND ss_store_sk = s_store_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,
    s_store_name,
    s_company_name,
    d_year,
    d_moy
), v2 AS (
  SELECT
    v1.i_category,
    v1.i_brand,
    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.s_store_name = v1_lag.s_store_name
    AND v1.s_store_name = v1_lead.s_store_name
    AND v1.s_company_name = v1_lag.s_company_name
    AND v1.s_company_name = v1_lead.s_company_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 q47_expression_impl(ctx: DataFrameContext) -> Any:
    return _rolling_average_expression_impl(
        ctx,
        query_id=47,
        sales_table="store_sales",
        date_sk_col="ss_sold_date_sk",
        item_sk_col="ss_item_sk",
        sales_price_col="ss_sales_price",
        channel_table="store",
        channel_join_key_left="ss_store_sk",
        channel_join_key_right="s_store_sk",
        channel_cols=["s_store_name", "s_company_name"],
        select_one_default="v1.i_category, v1.i_brand",
    )

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

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

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

Source

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