TPC-DS Q88 — Store Sales Time Period Counts

TPC-DS benchmark · all TPC-DS queries

Counts store sales by 8 half-hour time periods with demographics

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  *
FROM (
  SELECT
    COUNT(*) AS h8_30_to_9
  FROM store_sales, household_demographics, time_dim, store
  WHERE
    ss_sold_time_sk = time_dim.t_time_sk
    AND ss_hdemo_sk = household_demographics.hd_demo_sk
    AND ss_store_sk = s_store_sk
    AND time_dim.t_hour = 8
    AND time_dim.t_minute >= 30
    AND (
      (
        household_demographics.hd_dep_count = 3
        AND household_demographics.hd_vehicle_count <= 3 + 2
      )
      OR (
        household_demographics.hd_dep_count = 0
        AND household_demographics.hd_vehicle_count <= 0 + 2
      )
      OR (
        household_demographics.hd_dep_count = 1
        AND household_demographics.hd_vehicle_count <= 1 + 2
      )
    )
    AND store.s_store_name = 'ese'
) AS s1, (
  SELECT
    COUNT(*) AS h9_to_9_30
  FROM store_sales, household_demographics, time_dim, store
  WHERE
    ss_sold_time_sk = time_dim.t_time_sk
    AND ss_hdemo_sk = household_demographics.hd_demo_sk
    AND ss_store_sk = s_store_sk
    AND time_dim.t_hour = 9
    AND time_dim.t_minute < 30
    AND (
      (
        household_demographics.hd_dep_count = 3
        AND household_demographics.hd_vehicle_count <= 3 + 2
      )
      OR (
        household_demographics.hd_dep_count = 0
        AND household_demographics.hd_vehicle_count <= 0 + 2
      )
      OR (
        household_demographics.hd_dep_count = 1
        AND household_demographics.hd_vehicle_count <= 1 + 2
      )
    )
    AND store.s_store_name = 'ese'
) AS s2, (
  SELECT
    COUNT(*) AS h9_30_to_10
  FROM store_sales, household_demographics, time_dim, store
  WHERE
    ss_sold_time_sk = time_dim.t_time_sk
    AND ss_hdemo_sk = household_demographics.hd_demo_sk
    AND ss_store_sk = s_store_sk
    AND time_dim.t_hour = 9
    AND time_dim.t_minute >= 30
    AND (
      (
        household_demographics.hd_dep_count = 3
        AND household_demographics.hd_vehicle_count <= 3 + 2
      )
      OR (
        household_demographics.hd_dep_count = 0
        AND household_demographics.hd_vehicle_count <= 0 + 2
      )
      OR (
        household_demographics.hd_dep_count = 1
        AND household_demographics.hd_vehicle_count <= 1 + 2
      )
    )
    AND store.s_store_name = 'ese'
) AS s3, (
  SELECT
    COUNT(*) AS h10_to_10_30
  FROM store_sales, household_demographics, time_dim, store
  WHERE
    ss_sold_time_sk = time_dim.t_time_sk
    AND ss_hdemo_sk = household_demographics.hd_demo_sk
    AND ss_store_sk = s_store_sk
    AND time_dim.t_hour = 10
    AND time_dim.t_minute < 30
    AND (
      (
        household_demographics.hd_dep_count = 3
        AND household_demographics.hd_vehicle_count <= 3 + 2
      )
      OR (
        household_demographics.hd_dep_count = 0
        AND household_demographics.hd_vehicle_count <= 0 + 2
      )
      OR (
        household_demographics.hd_dep_count = 1
        AND household_demographics.hd_vehicle_count <= 1 + 2
      )
    )
    AND store.s_store_name = 'ese'
) AS s4, (
  SELECT
    COUNT(*) AS h10_30_to_11
  FROM store_sales, household_demographics, time_dim, store
  WHERE
    ss_sold_time_sk = time_dim.t_time_sk
    AND ss_hdemo_sk = household_demographics.hd_demo_sk
    AND ss_store_sk = s_store_sk
    AND time_dim.t_hour = 10
    AND time_dim.t_minute >= 30
    AND (
      (
        household_demographics.hd_dep_count = 3
        AND household_demographics.hd_vehicle_count <= 3 + 2
      )
      OR (
        household_demographics.hd_dep_count = 0
        AND household_demographics.hd_vehicle_count <= 0 + 2
      )
      OR (
        household_demographics.hd_dep_count = 1
        AND household_demographics.hd_vehicle_count <= 1 + 2
      )
    )
    AND store.s_store_name = 'ese'
) AS s5, (
  SELECT
    COUNT(*) AS h11_to_11_30
  FROM store_sales, household_demographics, time_dim, store
  WHERE
    ss_sold_time_sk = time_dim.t_time_sk
    AND ss_hdemo_sk = household_demographics.hd_demo_sk
    AND ss_store_sk = s_store_sk
    AND time_dim.t_hour = 11
    AND time_dim.t_minute < 30
    AND (
      (
        household_demographics.hd_dep_count = 3
        AND household_demographics.hd_vehicle_count <= 3 + 2
      )
      OR (
        household_demographics.hd_dep_count = 0
        AND household_demographics.hd_vehicle_count <= 0 + 2
      )
      OR (
        household_demographics.hd_dep_count = 1
        AND household_demographics.hd_vehicle_count <= 1 + 2
      )
    )
    AND store.s_store_name = 'ese'
) AS s6, (
  SELECT
    COUNT(*) AS h11_30_to_12
  FROM store_sales, household_demographics, time_dim, store
  WHERE
    ss_sold_time_sk = time_dim.t_time_sk
    AND ss_hdemo_sk = household_demographics.hd_demo_sk
    AND ss_store_sk = s_store_sk
    AND time_dim.t_hour = 11
    AND time_dim.t_minute >= 30
    AND (
      (
        household_demographics.hd_dep_count = 3
        AND household_demographics.hd_vehicle_count <= 3 + 2
      )
      OR (
        household_demographics.hd_dep_count = 0
        AND household_demographics.hd_vehicle_count <= 0 + 2
      )
      OR (
        household_demographics.hd_dep_count = 1
        AND household_demographics.hd_vehicle_count <= 1 + 2
      )
    )
    AND store.s_store_name = 'ese'
) AS s7, (
  SELECT
    COUNT(*) AS h12_to_12_30
  FROM store_sales, household_demographics, time_dim, store
  WHERE
    ss_sold_time_sk = time_dim.t_time_sk
    AND ss_hdemo_sk = household_demographics.hd_demo_sk
    AND ss_store_sk = s_store_sk
    AND time_dim.t_hour = 12
    AND time_dim.t_minute < 30
    AND (
      (
        household_demographics.hd_dep_count = 3
        AND household_demographics.hd_vehicle_count <= 3 + 2
      )
      OR (
        household_demographics.hd_dep_count = 0
        AND household_demographics.hd_vehicle_count <= 0 + 2
      )
      OR (
        household_demographics.hd_dep_count = 1
        AND household_demographics.hd_vehicle_count <= 1 + 2
      )
    )
    AND store.s_store_name = 'ese'
) AS s8

Representative DataFrame

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

def q88_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(88)
    dep_counts = _q88_dep_counts(params)
    store_name = _Q88_STORE_NAME

    store_sales, household_demographics, time_dim, store = _tables(
        ctx, "store_sales", "household_demographics", "time_dim", "store"
    )

    col = ctx.col
    lit = ctx.lit

    store_filtered = store.filter(col("s_store_name") == lit(store_name))

    hd_filters = None
    for dc in dep_counts:
        cond = (col("hd_dep_count") == lit(dc)) & (col("hd_vehicle_count") <= lit(dc + 2))
        hd_filters = cond if hd_filters is None else hd_filters | cond

    hd_filtered = household_demographics.filter(hd_filters)

    base = (
        store_sales.join(hd_filtered, left_on="ss_hdemo_sk", right_on="hd_demo_sk")
        .join(store_filtered, left_on="ss_store_sk", right_on="s_store_sk")
        .join(time_dim, left_on="ss_sold_time_sk", right_on="t_time_sk")
    )

    time_periods = [
        ("h8_30_to_9", 8, 30, 60),
        ("h9_to_9_30", 9, 0, 30),
        ("h9_30_to_10", 9, 30, 60),
        ("h10_to_10_30", 10, 0, 30),
        ("h10_30_to_11", 10, 30, 60),
        ("h11_to_11_30", 11, 0, 30),
        ("h11_30_to_12", 11, 30, 60),
        ("h12_to_12_30", 12, 0, 30),
    ]

    period_results = []
    for name, hour, min_start, min_end in time_periods:
        if min_end == 60:
            period_count = base.filter((col("t_hour") == lit(hour)) & (col("t_minute") >= lit(min_start))).select(
                ctx.len().alias(name)
            )
        else:
            period_count = base.filter((col("t_hour") == lit(hour)) & (col("t_minute") < lit(min_end))).select(
                ctx.len().alias(name)
            )
        period_results.append(period_count)

    result = period_results[0]
    for pr in period_results[1:]:
        result = result.join(pr, how="cross")

    return result

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

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

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

Source

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