TPC-DS Q63 — Store Manufacturer Profit

TPC-DS benchmark · all TPC-DS queries

Reports store profit by manufacturer for specific months

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  *
FROM (
  SELECT
    i_manager_id,
    SUM(ss_sales_price) AS sum_sales,
    AVG(SUM(ss_sales_price)) OVER (PARTITION BY i_manager_id) 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_month_seq IN (
      1212,
      1212 + 1,
      1212 + 2,
      1212 + 3,
      1212 + 4,
      1212 + 5,
      1212 + 6,
      1212 + 7,
      1212 + 8,
      1212 + 9,
      1212 + 10,
      1212 + 11
    )
    AND (
      (
        i_category IN ('Books', 'Children', 'Electronics')
        AND i_class IN ('personal', 'portable', 'reference', 'self-help')
        AND i_brand IN (
          'scholaramalgamalg #14',
          'scholaramalgamalg #7',
          'exportiunivamalg #9',
          'scholaramalgamalg #9'
        )
      )
      OR (
        i_category IN ('Women', 'Music', 'Men')
        AND i_class IN ('accessories', 'classical', 'fragrances', 'pants')
        AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1')
      )
    )
  GROUP BY
    i_manager_id,
    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
  i_manager_id,
  avg_monthly_sales,
  sum_sales
LIMIT 100

Representative DataFrame

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

def impl(ctx: DataFrameContext) -> Any:
        return helper(ctx, query_id, *helper_args)

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

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

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

Source

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