TPC-H Data Vault Q22 — Global Sales Opportunity

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

Identify customers likely to make purchases (Data Vault)

Representative SQL

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

SELECT
  cntrycode,
  COUNT(*) AS numcust,
  SUM(c_acctbal) AS totacctbal
FROM (
  SELECT
    SUBSTRING(sc.c_phone, 1, 2) AS cntrycode,
    sc.c_acctbal
  FROM hub_customer AS hc
  JOIN sat_customer AS sc
    ON hc.hk_customer = sc.hk_customer AND sc.load_end_dts IS NULL
  WHERE
    SUBSTRING(sc.c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17')
    AND sc.c_acctbal > (
      SELECT
        AVG(sc2.c_acctbal)
      FROM hub_customer AS hc2
      JOIN sat_customer AS sc2
        ON hc2.hk_customer = sc2.hk_customer AND sc2.load_end_dts IS NULL
      WHERE
        sc2.c_acctbal > 0.00
        AND SUBSTRING(sc2.c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17')
    )
    AND NOT EXISTS(
      SELECT
        1
      FROM link_order_customer AS loc
      WHERE
        loc.hk_customer = hc.hk_customer
    )
) AS custsale
GROUP BY
  cntrycode
ORDER BY
  cntrycode

Representative DataFrame

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

def q22_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q22")
    col, lit = ctx.col, ctx.lit
    codes = params.get("country_codes", ["13", "31", "23", "29", "30", "18", "17"])

    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"))

    customers = hc.join(sc, left_on="hk_customer", right_on="hk_customer")
    customers = customers.with_columns(col("c_phone").str.slice(0, 2).alias("cntrycode"))
    customers = customers.filter(col("cntrycode").is_in(codes))

    avg_bal = ctx.scalar(
        customers.filter(col("c_acctbal") > lit(0)).agg(col("c_acctbal").mean().alias("avg_bal")),
        "avg_bal",
    )

    has_orders = loc.select("hk_customer").unique()
    no_orders = customers.join(has_orders, left_on="hk_customer", right_on="hk_customer", how="anti")

    result = (
        no_orders.filter(col("c_acctbal") > lit(avg_bal))
        .group_by("cntrycode")
        .agg(
            col("hk_customer").count().alias("numcust"),
            col("c_acctbal").sum().alias("totacctbal"),
        )
    )
    return result.sort("cntrycode")

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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