TPC-Havoc Q8_v5

TPC-Havoc benchmark · all TPC-Havoc queries

Representative SQL

Rendered for DataFusion with default parameters.

WITH america_region AS (
  SELECT
    r_regionkey
  FROM region
  WHERE
    r_name = 'AMERICA'
), america_nations AS (
  SELECT
    n_nationkey,
    n_name
  FROM nation, america_region
  WHERE
    n_regionkey = r_regionkey
), qualified_customers AS (
  SELECT
    c_custkey,
    c_nationkey
  FROM customer, america_nations
  WHERE
    c_nationkey = n_nationkey
), qualified_orders AS (
  SELECT
    o_orderkey,
    o_custkey,
    o_orderdate
  FROM orders
  WHERE
    o_orderdate BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE)
), qualified_parts AS (
  SELECT
    p_partkey
  FROM part
  WHERE
    p_type = 'ECONOMY ANODIZED STEEL'
), qualified_lineitems AS (
  SELECT
    l_orderkey,
    l_partkey,
    l_suppkey,
    l_extendedprice,
    l_discount
  FROM lineitem, qualified_parts
  WHERE
    l_partkey = p_partkey
), market_data AS (
  SELECT
    EXTRACT(YEAR FROM qo.o_orderdate) AS o_year,
    ql.l_extendedprice * (
      1 - ql.l_discount
    ) AS volume,
    n.n_name AS nation
  FROM qualified_lineitems AS ql
  JOIN qualified_orders AS qo
    ON ql.l_orderkey = qo.o_orderkey
  JOIN qualified_customers AS qc
    ON qo.o_custkey = qc.c_custkey
  JOIN supplier AS s
    ON ql.l_suppkey = s.s_suppkey
  JOIN nation AS n
    ON s.s_nationkey = n.n_nationkey
)
SELECT
  o_year,
  SUM(CASE WHEN nation = 'BRAZIL' THEN volume ELSE 0 END) / SUM(volume) AS mkt_share
FROM market_data
GROUP BY
  o_year
ORDER BY
  o_year

Representative DataFrame

No DataFrame implementation is registered for this query.

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 TPCHavoc

bench = TPCHavoc(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_v5'])

Source

Query text: benchbox/core/tpchavoc/variant_sets/q08.py