TPC-DS Q89 — Store Item Sales Profit

TPC-DS benchmark · all TPC-DS queries

Reports monthly store sales average and deviation

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  *
FROM (
  SELECT
    i_category,
    i_class,
    i_brand,
    s_store_name,
    s_company_name,
    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) AS avg_monthly_sales
  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 IN (2000)
    AND (
      (
        i_category IN ('Home', 'Books', 'Electronics')
        AND i_class IN ('wallpaper', 'parenting', 'musical')
      )
      OR (
        i_category IN ('Shoes', 'Jewelry', 'Men')
        AND i_class IN ('womens', 'birdal', 'pants')
      )
    )
  GROUP BY
    i_category,
    i_class,
    i_brand,
    s_store_name,
    s_company_name,
    d_moy
) AS tmp1
WHERE
  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,
  s_store_name
LIMIT 100

Representative DataFrame

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

def q89_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(89)
    year = params.get("year", 2000)
    (categories_a, classes_a), (categories_b, classes_b) = _q89_groups(params)
    col = ctx.col
    lit = ctx.lit
    in_a = col("i_category").is_in(categories_a) & col("i_class").is_in(classes_a)
    in_b = col("i_category").is_in(categories_b) & col("i_class").is_in(classes_b)
    grouped = (
        ctx.get_table("item")
        .join(ctx.get_table("store_sales"), left_on="i_item_sk", right_on="ss_item_sk")
        .join(ctx.get_table("date_dim"), left_on="ss_sold_date_sk", right_on="d_date_sk")
        .join(ctx.get_table("store"), left_on="ss_store_sk", right_on="s_store_sk")
        .filter((col("d_year") == lit(year)) & (in_a | in_b))
        .group_by("i_category", "i_class", "i_brand", "s_store_name", "s_company_name", "d_moy")
        .agg(col("ss_sales_price").sum().alias("sum_sales"))
    )
    partition = ["i_category", "i_brand", "s_store_name", "s_company_name"]
    with_avg = grouped.with_columns(ctx.window_avg("sum_sales", partition_by=partition).alias("avg_monthly_sales"))
    deviation = (col("sum_sales") - col("avg_monthly_sales")).abs() / col("avg_monthly_sales")
    return (
        with_avg.filter((col("avg_monthly_sales") != lit(0)) & (deviation > lit(0.1)))
        .with_columns((col("sum_sales") - col("avg_monthly_sales")).alias("diff"))
        .sort(["diff", "s_store_name"], nulls_last=True)
        .select(
            "i_category",
            "i_class",
            "i_brand",
            "s_store_name",
            "s_company_name",
            "d_moy",
            "sum_sales",
            "avg_monthly_sales",
        )
        .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['89'])

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

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

Source

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