TPC-Havoc Q10_v5

TPC-Havoc benchmark · all TPC-Havoc queries

Representative SQL

Rendered for DataFusion with default parameters.

WITH qualified_orders AS (
  SELECT
    o_orderkey,
    o_custkey
  FROM orders
  WHERE
    o_orderdate >= CAST('1993-10-01' AS DATE)
    AND o_orderdate < CAST('1993-10-01' AS DATE) + INTERVAL '3 MONTH'
), returned_lineitems AS (
  SELECT
    l_orderkey,
    l_extendedprice,
    l_discount
  FROM lineitem
  WHERE
    l_returnflag = 'R'
), customer_nations AS (
  SELECT
    c_custkey,
    c_name,
    c_acctbal,
    c_address,
    c_phone,
    c_comment,
    n_name
  FROM customer, nation
  WHERE
    c_nationkey = n_nationkey
), customer_returns AS (
  SELECT
    cn.c_custkey,
    cn.c_name,
    SUM(rl.l_extendedprice * (
      1 - rl.l_discount
    )) AS revenue,
    cn.c_acctbal,
    cn.n_name,
    cn.c_address,
    cn.c_phone,
    cn.c_comment
  FROM customer_nations AS cn
  JOIN qualified_orders AS qo
    ON cn.c_custkey = qo.o_custkey
  JOIN returned_lineitems AS rl
    ON qo.o_orderkey = rl.l_orderkey
  GROUP BY
    cn.c_custkey,
    cn.c_name,
    cn.c_acctbal,
    cn.c_phone,
    cn.n_name,
    cn.c_address,
    cn.c_comment
)
SELECT
  c_custkey,
  c_name,
  revenue,
  c_acctbal,
  n_name,
  c_address,
  c_phone,
  c_comment
FROM customer_returns
ORDER BY
  revenue DESC

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['10_v5'])

Source

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