TPC-H Data Vault Q13 — Customer Distribution

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

Distribution of customers by order count (Data Vault)

Representative SQL

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

SELECT
  c_count,
  COUNT(*) AS custdist
FROM (
  SELECT
    hc.c_custkey,
    COUNT(so.hk_order) AS c_count
  FROM hub_customer AS hc
  LEFT JOIN link_order_customer AS loc
    ON hc.hk_customer = loc.hk_customer
  LEFT JOIN hub_order AS ho
    ON loc.hk_order = ho.hk_order
  LEFT JOIN sat_order AS so
    ON ho.hk_order = so.hk_order
    AND so.load_end_dts IS NULL
    AND so.o_comment NOT LIKE '%special%requests%'
  GROUP BY
    hc.c_custkey
) AS c_orders
GROUP BY
  c_count
ORDER BY
  custdist DESC,
  c_count DESC

Representative DataFrame

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

def q13_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q13")
    col = ctx.col
    word1 = params.get("word1", "special")
    word2 = params.get("word2", "requests")
    pattern = f"{word1}.*{word2}"

    hc = _strip_audit_columns(ctx.get_table("hub_customer"))
    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()))

    valid_orders = so.filter(~col("o_comment").str.contains(pattern))
    order_customer = loc.join(valid_orders, left_on="hk_order", right_on="hk_order")

    cust_orders = hc.join(order_customer, left_on="hk_customer", right_on="hk_customer", how="left")
    c_counts = cust_orders.group_by("c_custkey").agg(col("hk_order").count().alias("c_count"))

    result = c_counts.group_by("c_count").agg(col("c_custkey").count().alias("custdist"))
    return result.sort([("custdist", "desc"), ("c_count", "desc")])

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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