TPC-Havoc Q7_v5

TPC-Havoc benchmark · all TPC-Havoc queries

Representative SQL

Rendered for DataFusion with default parameters.

WITH qualified_suppliers AS (
  SELECT
    s_suppkey,
    n_name AS supp_nation
  FROM supplier, nation
  WHERE
    s_nationkey = n_nationkey AND n_name IN ('FRANCE', 'GERMANY')
), qualified_customers AS (
  SELECT
    c_custkey,
    n_name AS cust_nation
  FROM customer, nation
  WHERE
    c_nationkey = n_nationkey AND n_name IN ('FRANCE', 'GERMANY')
), qualified_orders AS (
  SELECT
    o_orderkey,
    o_custkey
  FROM orders
), shipping_base AS (
  SELECT
    qs.supp_nation,
    qc.cust_nation,
    EXTRACT(YEAR FROM l.l_shipdate) AS l_year,
    l.l_extendedprice * (
      1 - l.l_discount
    ) AS volume
  FROM qualified_suppliers AS qs
  JOIN lineitem AS l
    ON qs.s_suppkey = l.l_suppkey
  JOIN qualified_orders AS qo
    ON l.l_orderkey = qo.o_orderkey
  JOIN qualified_customers AS qc
    ON qo.o_custkey = qc.c_custkey
  WHERE
    l.l_shipdate BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE)
    AND (
      (
        qs.supp_nation = 'FRANCE' AND qc.cust_nation = 'GERMANY'
      )
      OR (
        qs.supp_nation = 'GERMANY' AND qc.cust_nation = 'FRANCE'
      )
    )
)
SELECT
  supp_nation,
  cust_nation,
  l_year,
  SUM(volume) AS revenue
FROM shipping_base
GROUP BY
  supp_nation,
  cust_nation,
  l_year
ORDER BY
  supp_nation,
  cust_nation,
  l_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['7_v5'])

Source

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