TPC-Havoc Q15_v10

TPC-Havoc benchmark · all TPC-Havoc queries

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  s_suppkey,
  s_name,
  s_address,
  s_phone,
  CASE
    WHEN revenue_calc.total_revenue IS NOT NULL
    THEN revenue_calc.total_revenue
    ELSE 0
  END AS total_revenue
FROM supplier, (
  SELECT
    l_suppkey,
    CASE
      WHEN SUM(
        CASE
          WHEN l_shipdate >= CAST('1996-01-01' AS DATE)
          AND l_shipdate < CAST('1996-01-01' AS DATE) + INTERVAL '3 MONTH'
          THEN l_extendedprice * (
            1 - l_discount
          )
          ELSE 0
        END
      ) > 0
      THEN SUM(
        CASE
          WHEN l_shipdate >= CAST('1996-01-01' AS DATE)
          AND l_shipdate < CAST('1996-01-01' AS DATE) + INTERVAL '3 MONTH'
          THEN l_extendedprice * (
            1 - l_discount
          )
          ELSE 0
        END
      )
      ELSE 0
    END AS total_revenue
  FROM lineitem
  WHERE
    l_shipdate >= CAST('1996-01-01' AS DATE)
    AND l_shipdate < CAST('1996-01-01' AS DATE) + INTERVAL '3 MONTH'
  GROUP BY
    l_suppkey
) AS revenue_calc
WHERE
  s_suppkey = revenue_calc.l_suppkey
  AND CASE
    WHEN revenue_calc.total_revenue = (
      SELECT
        MAX(max_calc.total_revenue)
      FROM (
        SELECT
          SUM(l_extendedprice * (
            1 - l_discount
          )) AS total_revenue
        FROM lineitem
        WHERE
          l_shipdate >= CAST('1996-01-01' AS DATE)
          AND l_shipdate < CAST('1996-01-01' AS DATE) + INTERVAL '3 MONTH'
        GROUP BY
          l_suppkey
      ) AS max_calc
    )
    THEN 1
    ELSE 0
  END = 1
ORDER BY
  s_suppkey

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['15_v10'])

Source

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