TPC-H Data Vault Q10 — Returned Item Reporting

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

Customers with returned parts and revenue impact (Data Vault)

Representative SQL

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

WITH lineitem_revenue_by_order AS (
  SELECT
    ll.hk_order,
    SUM(sl.l_extendedprice * (
      1 - sl.l_discount
    )) AS line_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_returnflag = 'R'
  GROUP BY
    ll.hk_order
), customer_revenue AS (
  SELECT
    loc.hk_customer,
    SUM(lr.line_revenue) AS customer_revenue
  FROM link_order_customer AS loc
  JOIN hub_order AS ho
    ON loc.hk_order = ho.hk_order
  JOIN sat_order AS so
    ON ho.hk_order = so.hk_order AND so.load_end_dts IS NULL
  JOIN lineitem_revenue_by_order AS lr
    ON ho.hk_order = lr.hk_order
  WHERE
    so.o_orderdate >= CAST('1993-10-01' AS DATE)
    AND so.o_orderdate < CAST('1993-10-01' AS DATE) + INTERVAL '3' MONTH
  GROUP BY
    loc.hk_customer
)
SELECT
  hc.c_custkey,
  sc.c_name,
  SUM(cr.customer_revenue) AS revenue,
  sc.c_acctbal,
  sn.n_name,
  sc.c_address,
  sc.c_phone,
  sc.c_comment
FROM customer_revenue AS cr
JOIN hub_customer AS hc
  ON cr.hk_customer = hc.hk_customer
JOIN sat_customer AS sc
  ON hc.hk_customer = sc.hk_customer AND sc.load_end_dts IS NULL
JOIN link_customer_nation AS lcn
  ON hc.hk_customer = lcn.hk_customer
JOIN hub_nation AS hn
  ON lcn.hk_nation = hn.hk_nation
JOIN sat_nation AS sn
  ON hn.hk_nation = sn.hk_nation AND sn.load_end_dts IS NULL
GROUP BY
  hc.c_custkey,
  sc.c_name,
  sc.c_acctbal,
  sc.c_phone,
  sn.n_name,
  sc.c_address,
  sc.c_comment
ORDER BY
  revenue DESC
LIMIT 20

Representative DataFrame

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

def q10_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q10")
    col, lit = ctx.col, ctx.lit
    start_date = params.get("start_date", date(1993, 10, 1))
    end_date = params.get("end_date", date(1994, 1, 1))

    hc = _strip_audit_columns(ctx.get_table("hub_customer"))
    sc = _strip_audit_columns(ctx.get_table("sat_customer").filter(col("load_end_dts").is_null()))
    loc = _strip_audit_columns(ctx.get_table("link_order_customer"))
    so = _strip_audit_columns(ctx.get_table("sat_order").filter(col("load_end_dts").is_null()))
    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()))
    lcn = _strip_audit_columns(ctx.get_table("link_customer_nation"))
    sn = _strip_audit_columns(ctx.get_table("sat_nation").filter(col("load_end_dts").is_null()))

    df = (
        hc.join(sc, left_on="hk_customer", right_on="hk_customer")
        .join(loc, left_on="hk_customer", right_on="hk_customer")
        .join(so, left_on="hk_order", right_on="hk_order")
        .filter((col("o_orderdate") >= lit(start_date)) & (col("o_orderdate") < lit(end_date)))
        .join(ll, left_on="hk_order", right_on="hk_order")
        .join(sl, left_on="hk_lineitem_link", right_on="hk_lineitem_link")
        .filter(col("l_returnflag") == lit("R"))
        .join(lcn, left_on="hk_customer", right_on="hk_customer")
        .join(sn, left_on="hk_nation", right_on="hk_nation")
    )

    df = df.with_columns((col("l_extendedprice") * (lit(1) - col("l_discount"))).alias("revenue"))
    result = df.group_by("c_custkey", "c_name", "c_acctbal", "c_phone", "n_name", "c_address", "c_comment").agg(
        col("revenue").sum().alias("revenue"),
    )
    result = result.select("c_custkey", "c_name", "revenue", "c_acctbal", "n_name", "c_address", "c_phone", "c_comment")
    return result.sort([("revenue", "desc")]).limit(20)

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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