TPC-DS Q61 — Promotional Sales Ratio

TPC-DS benchmark · all TPC-DS queries

Compares promotional sales to total sales for a category by GMT offset

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  promotions,
  total,
  CAST(promotions AS DECIMAL(15, 4)) / CAST(total AS DECIMAL(15, 4)) * 100
FROM (
  SELECT
    SUM(ss_ext_sales_price) AS promotions
  FROM store_sales, store, promotion, date_dim, customer, customer_address, item
  WHERE
    ss_sold_date_sk = d_date_sk
    AND ss_store_sk = s_store_sk
    AND ss_promo_sk = p_promo_sk
    AND ss_customer_sk = c_customer_sk
    AND ca_address_sk = c_current_addr_sk
    AND ss_item_sk = i_item_sk
    AND ca_gmt_offset = -7
    AND i_category = 'Books'
    AND (
      p_channel_dmail = 'Y' OR p_channel_email = 'Y' OR p_channel_tv = 'Y'
    )
    AND s_gmt_offset = -7
    AND d_year = 1999
    AND d_moy = 11
) AS promotional_sales, (
  SELECT
    SUM(ss_ext_sales_price) AS total
  FROM store_sales, store, date_dim, customer, customer_address, item
  WHERE
    ss_sold_date_sk = d_date_sk
    AND ss_store_sk = s_store_sk
    AND ss_customer_sk = c_customer_sk
    AND ca_address_sk = c_current_addr_sk
    AND ss_item_sk = i_item_sk
    AND ca_gmt_offset = -7
    AND i_category = 'Books'
    AND s_gmt_offset = -7
    AND d_year = 1999
    AND d_moy = 11
) AS all_sales
ORDER BY
  promotions,
  total
LIMIT 100

Representative DataFrame

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

def q61_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(61)
    year = params.get("year", 1998)
    month = params.get("month", 11)
    gmt_offset = params.get("gmt_offset", -5.0)
    category = params.get("category", "Jewelry")

    col = ctx.col
    lit = ctx.lit

    store_sales, store, promotion, date_dim, customer, customer_address, item = _tables(
        ctx, "store_sales", "store", "promotion", "date_dim", "customer", "customer_address", "item"
    )

    date_filtered = date_dim.filter((col("d_year") == lit(year)) & (col("d_moy") == lit(month)))
    store_filtered = store.filter(col("s_gmt_offset") == lit(gmt_offset))
    ca_filtered = customer_address.filter(col("ca_gmt_offset") == lit(gmt_offset))
    item_filtered = item.filter(col("i_category") == lit(category))
    promo_filtered = promotion.filter(
        (col("p_channel_dmail") == lit("Y")) | (col("p_channel_email") == lit("Y")) | (col("p_channel_tv") == lit("Y"))
    )

    base = (
        store_sales.join(date_filtered, left_on="ss_sold_date_sk", right_on="d_date_sk")
        .join(store_filtered, left_on="ss_store_sk", right_on="s_store_sk")
        .join(customer, left_on="ss_customer_sk", right_on="c_customer_sk")
        .join(ca_filtered, left_on="c_current_addr_sk", right_on="ca_address_sk")
        .join(item_filtered, left_on="ss_item_sk", right_on="i_item_sk")
    )

    total = base.select(
        ctx.when(col("ss_ext_sales_price").count() > lit(0))
        .then(col("ss_ext_sales_price").sum())
        .otherwise(lit(None))
        .alias("total")
    )

    promo_base = base.join(promo_filtered, left_on="ss_promo_sk", right_on="p_promo_sk")
    promo = promo_base.select(
        ctx.when(col("ss_ext_sales_price").count() > lit(0))
        .then(col("ss_ext_sales_price").sum())
        .otherwise(lit(None))
        .alias("promotions")
    )

    return promo.join(total, how="cross").with_columns(
        ((col("promotions") / col("total")) * lit(100)).alias("promo_pct")
    )

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

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

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

Source

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