TPC-DS Q2 — Web/Catalog Weekly Sales Year-over-Year

TPC-DS benchmark · all TPC-DS queries

Compares weekly web+catalog sales ratios year-over-year by day of week

Representative SQL

Rendered for DataFusion with default parameters.

WITH wscs AS (
  SELECT
    sold_date_sk,
    sales_price
  FROM (
    SELECT
      ws_sold_date_sk AS sold_date_sk,
      ws_ext_sales_price AS sales_price
    FROM web_sales
    UNION ALL
    SELECT
      cs_sold_date_sk AS sold_date_sk,
      cs_ext_sales_price AS sales_price
    FROM catalog_sales
  )
), wswscs AS (
  SELECT
    d_week_seq,
    SUM(CASE WHEN (
      d_day_name = 'Sunday'
    ) THEN sales_price ELSE NULL END) AS sun_sales,
    SUM(CASE WHEN (
      d_day_name = 'Monday'
    ) THEN sales_price ELSE NULL END) AS mon_sales,
    SUM(CASE WHEN (
      d_day_name = 'Tuesday'
    ) THEN sales_price ELSE NULL END) AS tue_sales,
    SUM(CASE WHEN (
      d_day_name = 'Wednesday'
    ) THEN sales_price ELSE NULL END) AS wed_sales,
    SUM(CASE WHEN (
      d_day_name = 'Thursday'
    ) THEN sales_price ELSE NULL END) AS thu_sales,
    SUM(CASE WHEN (
      d_day_name = 'Friday'
    ) THEN sales_price ELSE NULL END) AS fri_sales,
    SUM(CASE WHEN (
      d_day_name = 'Saturday'
    ) THEN sales_price ELSE NULL END) AS sat_sales
  FROM wscs, date_dim
  WHERE
    d_date_sk = sold_date_sk
  GROUP BY
    d_week_seq
)
SELECT
  d_week_seq1,
  ROUND(sun_sales1 / sun_sales2, 2),
  ROUND(mon_sales1 / mon_sales2, 2),
  ROUND(tue_sales1 / tue_sales2, 2),
  ROUND(wed_sales1 / wed_sales2, 2),
  ROUND(thu_sales1 / thu_sales2, 2),
  ROUND(fri_sales1 / fri_sales2, 2),
  ROUND(sat_sales1 / sat_sales2, 2)
FROM (
  SELECT
    wswscs.d_week_seq AS d_week_seq1,
    sun_sales AS sun_sales1,
    mon_sales AS mon_sales1,
    tue_sales AS tue_sales1,
    wed_sales AS wed_sales1,
    thu_sales AS thu_sales1,
    fri_sales AS fri_sales1,
    sat_sales AS sat_sales1
  FROM wswscs, date_dim
  WHERE
    date_dim.d_week_seq = wswscs.d_week_seq AND d_year = 2001
) AS y, (
  SELECT
    wswscs.d_week_seq AS d_week_seq2,
    sun_sales AS sun_sales2,
    mon_sales AS mon_sales2,
    tue_sales AS tue_sales2,
    wed_sales AS wed_sales2,
    thu_sales AS thu_sales2,
    fri_sales AS fri_sales2,
    sat_sales AS sat_sales2
  FROM wswscs, date_dim
  WHERE
    date_dim.d_week_seq = wswscs.d_week_seq AND d_year = 2001 + 1
) AS z
WHERE
  d_week_seq1 = d_week_seq2 - 53
ORDER BY
  d_week_seq1

Representative DataFrame

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

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

    params = get_parameters(2)
    year = params.get("year", 1998)

    web_sales, catalog_sales, date_dim = _tables(ctx, "web_sales", "catalog_sales", "date_dim")

    ws = web_sales.select(
        [
            col("ws_sold_date_sk").alias("sold_date_sk"),
            col("ws_ext_sales_price").alias("sales_price"),
        ]
    )
    cs = catalog_sales.select(
        [
            col("cs_sold_date_sk").alias("sold_date_sk"),
            col("cs_ext_sales_price").alias("sales_price"),
        ]
    )
    wscs = ctx.concat([ws, cs])

    joined = wscs.join(
        date_dim,
        left_on="sold_date_sk",
        right_on="d_date_sk",
        how="inner",
    )

    wswscs = joined.group_by("d_week_seq").agg(
        [
            ctx.when(col("d_day_name") == "Sunday").then(col("sales_price")).otherwise(None).sum().alias("sun_sales"),
            ctx.when(col("d_day_name") == "Monday").then(col("sales_price")).otherwise(None).sum().alias("mon_sales"),
            ctx.when(col("d_day_name") == "Tuesday").then(col("sales_price")).otherwise(None).sum().alias("tue_sales"),
            ctx.when(col("d_day_name") == "Wednesday")
            .then(col("sales_price"))
            .otherwise(None)
            .sum()
            .alias("wed_sales"),
            ctx.when(col("d_day_name") == "Thursday").then(col("sales_price")).otherwise(None).sum().alias("thu_sales"),
            ctx.when(col("d_day_name") == "Friday").then(col("sales_price")).otherwise(None).sum().alias("fri_sales"),
            ctx.when(col("d_day_name") == "Saturday").then(col("sales_price")).otherwise(None).sum().alias("sat_sales"),
            *[
                ctx.when(col("d_day_name") == day).then(col("sales_price")).otherwise(None).count().alias(f"n_{alias}")
                for day, alias in [
                    ("Sunday", "sun_sales"),
                    ("Monday", "mon_sales"),
                    ("Tuesday", "tue_sales"),
                    ("Wednesday", "wed_sales"),
                    ("Thursday", "thu_sales"),
                    ("Friday", "fri_sales"),
                    ("Saturday", "sat_sales"),
                ]
            ],
        ]
    )
    for _alias in ["sun_sales", "mon_sales", "tue_sales", "wed_sales", "thu_sales", "fri_sales", "sat_sales"]:
        wswscs = wswscs.with_columns(
            ctx.when(col(f"n_{_alias}") > lit(0)).then(col(_alias)).otherwise(None).alias(_alias)
        )

    date_weeks = date_dim.filter(col("d_year") == year).select(["d_week_seq"])
    y1 = wswscs.join(date_weeks, on="d_week_seq", how="inner").select(
        [
            col("d_week_seq").alias("d_week_seq1"),
            col("sun_sales").alias("sun_sales1"),
            col("mon_sales").alias("mon_sales1"),
            col("tue_sales").alias("tue_sales1"),
            col("wed_sales").alias("wed_sales1"),
            col("thu_sales").alias("thu_sales1"),
            col("fri_sales").alias("fri_sales1"),
            col("sat_sales").alias("sat_sales1"),
        ]
    )

    date_weeks_next = date_dim.filter(col("d_year") == year + 1).select(["d_week_seq"])
    y2 = wswscs.join(date_weeks_next, on="d_week_seq", how="inner").select(
        [
            col("d_week_seq").alias("d_week_seq2"),
            col("sun_sales").alias("sun_sales2"),
            col("mon_sales").alias("mon_sales2"),
            col("tue_sales").alias("tue_sales2"),
            col("wed_sales").alias("wed_sales2"),
            col("thu_sales").alias("thu_sales2"),
            col("fri_sales").alias("fri_sales2"),
            col("sat_sales").alias("sat_sales2"),
        ]
    )

    return (
        y1.join(
            y2,
            left_on="d_week_seq1",
            right_on=col("d_week_seq2") - 53,
            how="inner",
        )
        .select(
            [
                col("d_week_seq1"),
                (col("sun_sales1") / col("sun_sales2")).round(2).alias("sun_ratio"),
                (col("mon_sales1") / col("mon_sales2")).round(2).alias("mon_ratio"),
                (col("tue_sales1") / col("tue_sales2")).round(2).alias("tue_ratio"),
                (col("wed_sales1") / col("wed_sales2")).round(2).alias("wed_ratio"),
                (col("thu_sales1") / col("thu_sales2")).round(2).alias("thu_ratio"),
                (col("fri_sales1") / col("fri_sales2")).round(2).alias("fri_ratio"),
                (col("sat_sales1") / col("sat_sales2")).round(2).alias("sat_ratio"),
            ]
        )
        .sort("d_week_seq1")
    )

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

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

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

Source

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