TPC-H Data Vault Q15 — Top Supplier

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

Determine top supplier based on revenue (Data Vault)

Representative SQL

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

WITH revenue AS (
  SELECT
    ll.hk_supplier AS supplier_no,
    SUM(sl.l_extendedprice * (
      1 - sl.l_discount
    )) AS total_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
  WHERE
    sl.l_shipdate >= CAST('1996-01-01' AS DATE)
    AND sl.l_shipdate < CAST('1996-01-01' AS DATE) + INTERVAL '3' MONTH
  GROUP BY
    ll.hk_supplier
)
SELECT
  hs.s_suppkey,
  ss.s_name,
  ss.s_address,
  ss.s_phone,
  r.total_revenue
FROM hub_supplier AS hs
JOIN sat_supplier AS ss
  ON hs.hk_supplier = ss.hk_supplier AND ss.load_end_dts IS NULL
JOIN revenue AS r
  ON hs.hk_supplier = r.supplier_no
WHERE
  r.total_revenue = (
    SELECT
      MAX(total_revenue)
    FROM revenue
  )
ORDER BY
  hs.s_suppkey

Representative DataFrame

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

def q15_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q15")
    col, lit = ctx.col, ctx.lit
    start_date = params.get("start_date", date(1996, 1, 1))
    end_date = params.get("end_date", date(1996, 4, 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()))
    hs = _strip_audit_columns(ctx.get_table("hub_supplier"))
    ss = _strip_audit_columns(ctx.get_table("sat_supplier").filter(col("load_end_dts").is_null()))

    revenue = (
        ll.join(sl, left_on="hk_lineitem_link", right_on="hk_lineitem_link")
        .filter((col("l_shipdate") >= lit(start_date)) & (col("l_shipdate") < lit(end_date)))
        .with_columns((col("l_extendedprice") * (lit(1) - col("l_discount"))).alias("revenue"))
        .group_by("hk_supplier")
        .agg(col("revenue").sum().alias("total_revenue"))
    )

    max_rev = ctx.scalar(revenue.agg(col("total_revenue").max().alias("max_rev")), "max_rev")

    suppliers = hs.join(ss, left_on="hk_supplier", right_on="hk_supplier").join(
        revenue, left_on="hk_supplier", right_on="hk_supplier"
    )
    if max_rev is None:
        return (
            suppliers.filter(lit(False))
            .select("s_suppkey", "s_name", "s_address", "s_phone", "total_revenue")
            .sort("s_suppkey")
        )
    return (
        suppliers.filter((col("total_revenue") - lit(max_rev)).abs() < lit(5e-5))
        .select("s_suppkey", "s_name", "s_address", "s_phone", "total_revenue")
        .sort("s_suppkey")
    )

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[15])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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