TPC-DS Q93 — Store Sales Returns Actual Sales

TPC-DS benchmark · all TPC-DS queries

Store sales with returns computing actual sales by customer filtered by reason

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  ss_customer_sk,
  SUM(act_sales) AS sumsales
FROM (
  SELECT
    ss_item_sk,
    ss_ticket_number,
    ss_customer_sk,
    CASE
      WHEN sr_return_quantity IS NOT NULL
      THEN (
        ss_quantity - sr_return_quantity
      ) * ss_sales_price
      ELSE (
        ss_quantity * ss_sales_price
      )
    END AS act_sales
  FROM store_sales
  LEFT OUTER JOIN store_returns
    ON (
      sr_item_sk = ss_item_sk AND sr_ticket_number = ss_ticket_number
    ), reason
  WHERE
    sr_reason_sk = r_reason_sk AND r_reason_desc = 'Did not like the warranty'
) AS t
GROUP BY
  ss_customer_sk
ORDER BY
  sumsales,
  ss_customer_sk
LIMIT 100

Representative DataFrame

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

def q93_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(93)
    reason = params.get("reason", "reason 28")

    store_sales, store_returns = _tables(ctx, "store_sales", "store_returns")
    reason_table = ctx.get_table("reason")
    col = ctx.col
    lit = ctx.lit

    reason_filtered = reason_table.filter(col("r_reason_desc") == lit(reason))

    ss_with_sr = store_sales.join(
        store_returns,
        left_on=["ss_item_sk", "ss_ticket_number"],
        right_on=["sr_item_sk", "sr_ticket_number"],
        how="left",
    )

    ss_with_reason = ss_with_sr.join(reason_filtered, left_on="sr_reason_sk", right_on="r_reason_sk")

    result = ss_with_reason.with_columns(
        ctx.when(col("sr_return_quantity").is_not_null())
        .then((col("ss_quantity") - col("sr_return_quantity")) * col("ss_sales_price"))
        .otherwise(col("ss_quantity") * col("ss_sales_price"))
        .alias("act_sales")
    )

    return _sort_null_largest_expression(
        ctx,
        result.group_by("ss_customer_sk").agg(
            ctx.when(col("act_sales").count() > lit(0))
            .then(col("act_sales").sum())
            .otherwise(lit(None))
            .alias("sumsales")
        ),
        ["sumsales", "ss_customer_sk"],
    ).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['93'])

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

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

Source

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