TPC-DS Q66 — Web/Catalog Warehouse Monthly Sales

TPC-DS benchmark · all TPC-DS queries

Union of web and catalog sales aggregated by warehouse and month

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  w_warehouse_name,
  w_warehouse_sq_ft,
  w_city,
  w_county,
  w_state,
  w_country,
  ship_carriers,
  year,
  SUM(jan_sales) AS jan_sales,
  SUM(feb_sales) AS feb_sales,
  SUM(mar_sales) AS mar_sales,
  SUM(apr_sales) AS apr_sales,
  SUM(may_sales) AS may_sales,
  SUM(jun_sales) AS jun_sales,
  SUM(jul_sales) AS jul_sales,
  SUM(aug_sales) AS aug_sales,
  SUM(sep_sales) AS sep_sales,
  SUM(oct_sales) AS oct_sales,
  SUM(nov_sales) AS nov_sales,
  SUM(dec_sales) AS dec_sales,
  SUM(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot,
  SUM(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot,
  SUM(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot,
  SUM(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot,
  SUM(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot,
  SUM(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot,
  SUM(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot,
  SUM(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot,
  SUM(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot,
  SUM(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot,
  SUM(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot,
  SUM(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot,
  SUM(jan_net) AS jan_net,
  SUM(feb_net) AS feb_net,
  SUM(mar_net) AS mar_net,
  SUM(apr_net) AS apr_net,
  SUM(may_net) AS may_net,
  SUM(jun_net) AS jun_net,
  SUM(jul_net) AS jul_net,
  SUM(aug_net) AS aug_net,
  SUM(sep_net) AS sep_net,
  SUM(oct_net) AS oct_net,
  SUM(nov_net) AS nov_net,
  SUM(dec_net) AS dec_net
FROM (
  SELECT
    w_warehouse_name,
    w_warehouse_sq_ft,
    w_city,
    w_county,
    w_state,
    w_country,
    'DIAMOND' || ',' || 'AIRBORNE' AS ship_carriers,
    d_year AS year,
    SUM(CASE WHEN d_moy = 1 THEN ws_sales_price * ws_quantity ELSE 0 END) AS jan_sales,
    SUM(CASE WHEN d_moy = 2 THEN ws_sales_price * ws_quantity ELSE 0 END) AS feb_sales,
    SUM(CASE WHEN d_moy = 3 THEN ws_sales_price * ws_quantity ELSE 0 END) AS mar_sales,
    SUM(CASE WHEN d_moy = 4 THEN ws_sales_price * ws_quantity ELSE 0 END) AS apr_sales,
    SUM(CASE WHEN d_moy = 5 THEN ws_sales_price * ws_quantity ELSE 0 END) AS may_sales,
    SUM(CASE WHEN d_moy = 6 THEN ws_sales_price * ws_quantity ELSE 0 END) AS jun_sales,
    SUM(CASE WHEN d_moy = 7 THEN ws_sales_price * ws_quantity ELSE 0 END) AS jul_sales,
    SUM(CASE WHEN d_moy = 8 THEN ws_sales_price * ws_quantity ELSE 0 END) AS aug_sales,
    SUM(CASE WHEN d_moy = 9 THEN ws_sales_price * ws_quantity ELSE 0 END) AS sep_sales,
    SUM(CASE WHEN d_moy = 10 THEN ws_sales_price * ws_quantity ELSE 0 END) AS oct_sales,
    SUM(CASE WHEN d_moy = 11 THEN ws_sales_price * ws_quantity ELSE 0 END) AS nov_sales,
    SUM(CASE WHEN d_moy = 12 THEN ws_sales_price * ws_quantity ELSE 0 END) AS dec_sales,
    SUM(CASE WHEN d_moy = 1 THEN ws_net_paid_inc_tax * ws_quantity ELSE 0 END) AS jan_net,
    SUM(CASE WHEN d_moy = 2 THEN ws_net_paid_inc_tax * ws_quantity ELSE 0 END) AS feb_net,
    SUM(CASE WHEN d_moy = 3 THEN ws_net_paid_inc_tax * ws_quantity ELSE 0 END) AS mar_net,
    SUM(CASE WHEN d_moy = 4 THEN ws_net_paid_inc_tax * ws_quantity ELSE 0 END) AS apr_net,
    SUM(CASE WHEN d_moy = 5 THEN ws_net_paid_inc_tax * ws_quantity ELSE 0 END) AS may_net,
    SUM(CASE WHEN d_moy = 6 THEN ws_net_paid_inc_tax * ws_quantity ELSE 0 END) AS jun_net,
    SUM(CASE WHEN d_moy = 7 THEN ws_net_paid_inc_tax * ws_quantity ELSE 0 END) AS jul_net,
    SUM(CASE WHEN d_moy = 8 THEN ws_net_paid_inc_tax * ws_quantity ELSE 0 END) AS aug_net,
    SUM(CASE WHEN d_moy = 9 THEN ws_net_paid_inc_tax * ws_quantity ELSE 0 END) AS sep_net,
    SUM(CASE WHEN d_moy = 10 THEN ws_net_paid_inc_tax * ws_quantity ELSE 0 END) AS oct_net,
    SUM(CASE WHEN d_moy = 11 THEN ws_net_paid_inc_tax * ws_quantity ELSE 0 END) AS nov_net,
    SUM(CASE WHEN d_moy = 12 THEN ws_net_paid_inc_tax * ws_quantity ELSE 0 END) AS dec_net
  FROM web_sales, warehouse, date_dim, time_dim, ship_mode
  WHERE
    ws_warehouse_sk = w_warehouse_sk
    AND ws_sold_date_sk = d_date_sk
    AND ws_sold_time_sk = t_time_sk
    AND ws_ship_mode_sk = sm_ship_mode_sk
    AND d_year = 2002
    AND t_time BETWEEN 49530 AND 49530 + 28800
    AND sm_carrier IN ('DIAMOND', 'AIRBORNE')
  GROUP BY
    w_warehouse_name,
    w_warehouse_sq_ft,
    w_city,
    w_county,
    w_state,
    w_country,
    d_year
  UNION ALL
  SELECT
    w_warehouse_name,
    w_warehouse_sq_ft,
    w_city,
    w_county,
    w_state,
    w_country,
    'DIAMOND' || ',' || 'AIRBORNE' AS ship_carriers,
    d_year AS year,
    SUM(CASE WHEN d_moy = 1 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS jan_sales,
    SUM(CASE WHEN d_moy = 2 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS feb_sales,
    SUM(CASE WHEN d_moy = 3 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS mar_sales,
    SUM(CASE WHEN d_moy = 4 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS apr_sales,
    SUM(CASE WHEN d_moy = 5 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS may_sales,
    SUM(CASE WHEN d_moy = 6 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS jun_sales,
    SUM(CASE WHEN d_moy = 7 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS jul_sales,
    SUM(CASE WHEN d_moy = 8 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS aug_sales,
    SUM(CASE WHEN d_moy = 9 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS sep_sales,
    SUM(CASE WHEN d_moy = 10 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS oct_sales,
    SUM(CASE WHEN d_moy = 11 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS nov_sales,
    SUM(CASE WHEN d_moy = 12 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS dec_sales,
    SUM(CASE WHEN d_moy = 1 THEN cs_net_paid_inc_ship_tax * cs_quantity ELSE 0 END) AS jan_net,
    SUM(CASE WHEN d_moy = 2 THEN cs_net_paid_inc_ship_tax * cs_quantity ELSE 0 END) AS feb_net,
    SUM(CASE WHEN d_moy = 3 THEN cs_net_paid_inc_ship_tax * cs_quantity ELSE 0 END) AS mar_net,
    SUM(CASE WHEN d_moy = 4 THEN cs_net_paid_inc_ship_tax * cs_quantity ELSE 0 END) AS apr_net,
    SUM(CASE WHEN d_moy = 5 THEN cs_net_paid_inc_ship_tax * cs_quantity ELSE 0 END) AS may_net,
    SUM(CASE WHEN d_moy = 6 THEN cs_net_paid_inc_ship_tax * cs_quantity ELSE 0 END) AS jun_net,
    SUM(CASE WHEN d_moy = 7 THEN cs_net_paid_inc_ship_tax * cs_quantity ELSE 0 END) AS jul_net,
    SUM(CASE WHEN d_moy = 8 THEN cs_net_paid_inc_ship_tax * cs_quantity ELSE 0 END) AS aug_net,
    SUM(CASE WHEN d_moy = 9 THEN cs_net_paid_inc_ship_tax * cs_quantity ELSE 0 END) AS sep_net,
    SUM(CASE WHEN d_moy = 10 THEN cs_net_paid_inc_ship_tax * cs_quantity ELSE 0 END) AS oct_net,
    SUM(CASE WHEN d_moy = 11 THEN cs_net_paid_inc_ship_tax * cs_quantity ELSE 0 END) AS nov_net,
    SUM(CASE WHEN d_moy = 12 THEN cs_net_paid_inc_ship_tax * cs_quantity ELSE 0 END) AS dec_net
  FROM catalog_sales, warehouse, date_dim, time_dim, ship_mode
  WHERE
    cs_warehouse_sk = w_warehouse_sk
    AND cs_sold_date_sk = d_date_sk
    AND cs_sold_time_sk = t_time_sk
    AND cs_ship_mode_sk = sm_ship_mode_sk
    AND d_year = 2002
    AND t_time BETWEEN 49530 AND 49530 + 28800
    AND sm_carrier IN ('DIAMOND', 'AIRBORNE')
  GROUP BY
    w_warehouse_name,
    w_warehouse_sq_ft,
    w_city,
    w_county,
    w_state,
    w_country,
    d_year
) AS x
GROUP BY
  w_warehouse_name,
  w_warehouse_sq_ft,
  w_city,
  w_county,
  w_state,
  w_country,
  ship_carriers,
  year
ORDER BY
  w_warehouse_name
LIMIT 100

Representative DataFrame

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

def q66_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(66)
    year = params.get("year", 2002)
    ship_carriers = params.get("ship_carriers", ["DIAMOND", "AIRBORNE"])
    time_start = params.get("time_start", 49530)
    value_columns = _q66_value_columns(params)

    col = ctx.col
    lit = ctx.lit

    web_sales, catalog_sales, warehouse, date_dim, time_dim, ship_mode = _tables(
        ctx, "web_sales", "catalog_sales", "warehouse", "date_dim", "time_dim", "ship_mode"
    )

    date_filtered = date_dim.filter(col("d_year") == lit(year))
    time_filtered = time_dim.filter((col("t_time") >= lit(time_start)) & (col("t_time") <= lit(time_start + 28800)))
    sm_filtered = ship_mode.filter(col("sm_carrier").is_in(ship_carriers))
    carriers_str = ",".join(ship_carriers)

    def build_monthly_aggs(sales_col: str, net_col: str, qty_col: str):
        aggs = []
        for month in range(1, 13):
            month_names = ["jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec"]
            mname = month_names[month - 1]
            aggs.append(
                ctx.when(col("d_moy") == lit(month))
                .then(col(sales_col) * col(qty_col))
                .otherwise(lit(0))
                .sum()
                .alias(f"{mname}_sales")
            )
            aggs.append(
                ctx.when(col("d_moy") == lit(month))
                .then(col(net_col) * col(qty_col))
                .otherwise(lit(0))
                .sum()
                .alias(f"{mname}_net")
            )
        return aggs

    ws = (
        web_sales.join(warehouse, left_on="ws_warehouse_sk", right_on="w_warehouse_sk")
        .join(date_filtered, left_on="ws_sold_date_sk", right_on="d_date_sk")
        .join(time_filtered, left_on="ws_sold_time_sk", right_on="t_time_sk")
        .join(sm_filtered, left_on="ws_ship_mode_sk", right_on="sm_ship_mode_sk")
    )

    ws_agg = (
        ws.group_by(["w_warehouse_name", "w_warehouse_sq_ft", "w_city", "w_county", "w_state", "w_country", "d_year"])
        .agg(build_monthly_aggs(*value_columns["web"], "ws_quantity"))
        .with_columns(
            [
                lit(carriers_str).alias("ship_carriers"),
                col("d_year").alias("year"),
            ]
        )
    )

    cs = (
        catalog_sales.join(warehouse, left_on="cs_warehouse_sk", right_on="w_warehouse_sk")
        .join(date_filtered, left_on="cs_sold_date_sk", right_on="d_date_sk")
        .join(time_filtered, left_on="cs_sold_time_sk", right_on="t_time_sk")
        .join(sm_filtered, left_on="cs_ship_mode_sk", right_on="sm_ship_mode_sk")
    )

    cs_agg = (
        cs.group_by(["w_warehouse_name", "w_warehouse_sq_ft", "w_city", "w_county", "w_state", "w_country", "d_year"])
        .agg(build_monthly_aggs(*value_columns["catalog"], "cs_quantity"))
        .with_columns(
            [
                lit(carriers_str).alias("ship_carriers"),
                col("d_year").alias("year"),
            ]
        )
    )

    combined = ctx.concat([ws_agg, cs_agg])

    group_cols = [
        "w_warehouse_name",
        "w_warehouse_sq_ft",
        "w_city",
        "w_county",
        "w_state",
        "w_country",
        "ship_carriers",
        "year",
    ]

    month_names = ["jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec"]
    agg_exprs = []
    for mname in month_names:
        agg_exprs.append(ctx.sum(f"{mname}_sales").alias(f"{mname}_sales"))
    for mname in month_names:
        agg_exprs.append(ctx.sum(f"{mname}_net").alias(f"{mname}_net"))

    grouped = combined.group_by(group_cols).agg(agg_exprs)
    per_foot = [
        (col(f"{mname}_sales") / col("w_warehouse_sq_ft")).alias(f"{mname}_sales_per_sq_foot") for mname in month_names
    ]
    ordered = (
        group_cols
        + [f"{m}_sales" for m in month_names]
        + [f"{m}_sales_per_sq_foot" for m in month_names]
        + [f"{m}_net" for m in month_names]
    )
    return grouped.with_columns(per_foot).select(ordered).sort("w_warehouse_name", nulls_last=True).head(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['66'])

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

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

Source

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