TPC-H Data Vault Q8 — National Market Share

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

Market share of a nation within a region (Data Vault)

Representative SQL

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

SELECT
  o_year,
  SUM(CASE WHEN nation = 'BRAZIL' THEN volume ELSE 0 END) / SUM(volume) AS mkt_share
FROM (
  SELECT
    EXTRACT(YEAR FROM so.o_orderdate) AS o_year,
    sl.l_extendedprice * (
      1 - sl.l_discount
    ) AS volume,
    sn2.n_name AS nation
  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
  JOIN hub_supplier AS hs
    ON ll.hk_supplier = hs.hk_supplier
  JOIN link_supplier_nation AS lsn
    ON hs.hk_supplier = lsn.hk_supplier
  JOIN hub_nation AS hn2
    ON lsn.hk_nation = hn2.hk_nation
  JOIN sat_nation AS sn2
    ON hn2.hk_nation = sn2.hk_nation AND sn2.load_end_dts IS NULL
  JOIN hub_order AS ho
    ON ll.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 link_order_customer AS loc
    ON ho.hk_order = loc.hk_order
  JOIN hub_customer AS hc
    ON loc.hk_customer = hc.hk_customer
  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 link_nation_region AS lnr
    ON hn.hk_nation = lnr.hk_nation
  JOIN hub_region AS hr
    ON lnr.hk_region = hr.hk_region
  JOIN sat_region AS sr
    ON hr.hk_region = sr.hk_region AND sr.load_end_dts IS NULL
  WHERE
    sr.r_name = 'AMERICA'
    AND so.o_orderdate BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE)
    AND sp.p_type = 'ECONOMY ANODIZED STEEL'
) AS all_nations
GROUP BY
  o_year
ORDER BY
  o_year

Representative DataFrame

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

def q8_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q8")
    col, lit = ctx.col, ctx.lit
    target_nation = params.get("nation", "BRAZIL")
    target_region = params.get("region", "AMERICA")
    part_type = params.get("part_type", "ECONOMY ANODIZED STEEL")

    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()))
    lsn = _strip_audit_columns(ctx.get_table("link_supplier_nation"))
    sn = _strip_audit_columns(ctx.get_table("sat_nation").filter(col("load_end_dts").is_null()))
    so = _strip_audit_columns(ctx.get_table("sat_order").filter(col("load_end_dts").is_null()))
    loc = _strip_audit_columns(ctx.get_table("link_order_customer"))
    lcn = _strip_audit_columns(ctx.get_table("link_customer_nation"))
    lnr = _strip_audit_columns(ctx.get_table("link_nation_region"))
    sr = _strip_audit_columns(ctx.get_table("sat_region").filter(col("load_end_dts").is_null()))

    lineitem = (
        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("p_type") == lit(part_type))
    )

    supp_nation = lsn.join(sn, left_on="hk_nation", right_on="hk_nation").rename({"n_name": "supp_nation"})

    cust_region = (
        loc.join(lcn, left_on="hk_customer", right_on="hk_customer")
        .join(lnr, left_on="hk_nation", right_on="hk_nation")
        .join(sr, left_on="hk_region", right_on="hk_region")
        .filter(col("r_name") == lit(target_region))
    )

    df = (
        lineitem.join(so, left_on="hk_order", right_on="hk_order")
        .filter((col("o_orderdate") >= lit(date(1995, 1, 1))) & (col("o_orderdate") <= lit(date(1996, 12, 31))))
        .join(cust_region, left_on="hk_order", right_on="hk_order")
        .join(supp_nation, left_on="hk_supplier", right_on="hk_supplier")
    )

    df = df.with_columns(col("o_orderdate").dt.year().alias("o_year")).with_columns(
        (col("l_extendedprice") * (lit(1) - col("l_discount"))).alias("volume")
    )
    df = df.with_columns(
        ctx.when(col("supp_nation") == lit(target_nation)).then(col("volume")).otherwise(lit(0)).alias("nation_vol")
    )

    result = df.group_by("o_year").agg(
        col("nation_vol").sum().alias("nation_vol"),
        col("volume").sum().alias("total_vol"),
    )
    result = result.with_columns((col("nation_vol") / col("total_vol")).alias("mkt_share"))
    return result.select("o_year", "mkt_share").sort("o_year")

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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