TPC-H Data Vault Q14 — Promotion Effect

TPC-H Data Vault benchmark · all TPC-H Data Vault queries

Effect of promotions on revenue (Data Vault)

Representative SQL

Rendered in the benchmark’s native SQL with default parameters (no dialect translation available).

SELECT
  100.00 * SUM(
    CASE
      WHEN sp.p_type LIKE 'PROMO%'
      THEN sl.l_extendedprice * (
        1 - sl.l_discount
      )
      ELSE 0
    END
  ) / SUM(sl.l_extendedprice * (
    1 - sl.l_discount
  )) AS promo_revenue
FROM link_lineitem AS ll
JOIN sat_lineitem AS sl
  ON ll.hk_lineitem_link = sl.hk_lineitem_link AND sl.load_end_dts IS NULL
JOIN hub_part AS hp
  ON ll.hk_part = hp.hk_part
JOIN sat_part AS sp
  ON hp.hk_part = sp.hk_part AND sp.load_end_dts IS NULL
WHERE
  sl.l_shipdate >= CAST('1995-09-01' AS DATE)
  AND sl.l_shipdate < CAST('1995-09-01' AS DATE) + INTERVAL '1' MONTH

Representative DataFrame

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

def q14_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q14")
    col, lit = ctx.col, ctx.lit
    start_date = params.get("start_date", date(1995, 9, 1))
    end_date = params.get("end_date", date(1995, 10, 1))

    ll = _strip_audit_columns(ctx.get_table("link_lineitem"))
    sl = _strip_audit_columns(ctx.get_table("sat_lineitem").filter(col("load_end_dts").is_null()))
    sp = _strip_audit_columns(ctx.get_table("sat_part").filter(col("load_end_dts").is_null()))

    df = (
        ll.join(sl, left_on="hk_lineitem_link", right_on="hk_lineitem_link")
        .join(sp, left_on="hk_part", right_on="hk_part")
        .filter((col("l_shipdate") >= lit(start_date)) & (col("l_shipdate") < lit(end_date)))
    )

    df = df.with_columns((col("l_extendedprice") * (lit(1) - col("l_discount"))).alias("revenue"))
    df = df.with_columns(
        ctx.when(col("p_type").str.starts_with("PROMO")).then(col("revenue")).otherwise(lit(0)).alias("promo_revenue")
    )
    return df.agg(
        col("promo_revenue").sum().alias("promo_sum"),
        col("revenue").sum().alias("revenue_sum"),
        col("revenue").count().alias("n"),
    ).select(
        ctx.when(col("n") > lit(0))
        .then(col("promo_sum") * lit(100.0) / col("revenue_sum"))
        .otherwise(lit(None))
        .alias("promo_revenue")
    )

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 DataVault

bench = DataVault(scale_factor=0.01)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries[14])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('datavault', '14', family="expression").source)  # or family="pandas"

Source

Query text: benchbox/core/datavault/queries.py