TPC-DS Q71 — Three-Channel Sales by Brand and Time

TPC-DS benchmark · all TPC-DS queries

Union of web/catalog/store sales by brand and meal time

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  i_brand_id AS brand_id,
  i_brand AS brand,
  t_hour,
  t_minute,
  SUM(ext_price) AS ext_price
FROM item, (
  SELECT
    ws_ext_sales_price AS ext_price,
    ws_sold_date_sk AS sold_date_sk,
    ws_item_sk AS sold_item_sk,
    ws_sold_time_sk AS time_sk
  FROM web_sales, date_dim
  WHERE
    d_date_sk = ws_sold_date_sk AND d_moy = 12 AND d_year = 2000
  UNION ALL
  SELECT
    cs_ext_sales_price AS ext_price,
    cs_sold_date_sk AS sold_date_sk,
    cs_item_sk AS sold_item_sk,
    cs_sold_time_sk AS time_sk
  FROM catalog_sales, date_dim
  WHERE
    d_date_sk = cs_sold_date_sk AND d_moy = 12 AND d_year = 2000
  UNION ALL
  SELECT
    ss_ext_sales_price AS ext_price,
    ss_sold_date_sk AS sold_date_sk,
    ss_item_sk AS sold_item_sk,
    ss_sold_time_sk AS time_sk
  FROM store_sales, date_dim
  WHERE
    d_date_sk = ss_sold_date_sk AND d_moy = 12 AND d_year = 2000
) AS tmp, time_dim
WHERE
  sold_item_sk = i_item_sk
  AND i_manager_id = 1
  AND time_sk = t_time_sk
  AND (
    t_meal_time = 'breakfast' OR t_meal_time = 'dinner'
  )
GROUP BY
  i_brand,
  i_brand_id,
  t_hour,
  t_minute
ORDER BY
  ext_price DESC,
  i_brand_id

Representative DataFrame

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

def q71_expression_impl(ctx: DataFrameContext) -> Any:
    col = ctx.col

    params = get_parameters(71)
    year = params.get("year", 2000)
    month = params.get("month", 12)

    date_dim, item, time_dim = _tables(ctx, "date_dim", "item", "time_dim")

    date_filter = date_dim.filter((col("d_year") == year) & (col("d_moy") == month))

    def channel(table: str, date_key: str, price_col: str, item_key: str, time_key: str) -> Any:
        return (
            ctx.get_table(table)
            .join(date_filter, left_on=date_key, right_on="d_date_sk", how="inner")
            .select(
                [
                    col(price_col).alias("ext_price"),
                    col(item_key).alias("sold_item_sk"),
                    col(time_key).alias("time_sk"),
                ]
            )
        )

    combined = ctx.concat(
        [
            channel("web_sales", "ws_sold_date_sk", "ws_ext_sales_price", "ws_item_sk", "ws_sold_time_sk"),
            channel("catalog_sales", "cs_sold_date_sk", "cs_ext_sales_price", "cs_item_sk", "cs_sold_time_sk"),
            channel("store_sales", "ss_sold_date_sk", "ss_ext_sales_price", "ss_item_sk", "ss_sold_time_sk"),
        ]
    )
    grouped = (
        combined.join(item.filter(col("i_manager_id") == 1), left_on="sold_item_sk", right_on="i_item_sk", how="inner")
        .join(
            time_dim.filter(col("t_meal_time").is_in(["breakfast", "dinner"])),
            left_on="time_sk",
            right_on="t_time_sk",
            how="inner",
        )
        .group_by(["i_brand_id", "i_brand", "t_hour", "t_minute"])
        .agg(
            ctx.when(col("ext_price").count() > ctx.lit(0))
            .then(col("ext_price").sum())
            .otherwise(ctx.lit(None))
            .alias("ext_price")
        )
    )
    return _sort_null_largest_expression(ctx, grouped, ["ext_price", "i_brand_id"], [True, False])

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

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

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

Source

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