TPC-H Skew Q15 — Top Supplier

TPC-H Skew benchmark · all TPC-H Skew queries

Determine top supplier based on revenue

Representative SQL

Rendered for DataFusion with default parameters.

WITH revenue(supplier_no, total_revenue) AS (
  SELECT
    l_suppkey,
    SUM(l_extendedprice * (
      1 - l_discount
    ))
  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
)
SELECT
  s_suppkey,
  s_name,
  s_address,
  s_phone,
  total_revenue
FROM supplier, revenue
WHERE
  s_suppkey = supplier_no
  AND total_revenue = (
    SELECT
      MAX(total_revenue)
    FROM revenue
  )
ORDER BY
  s_suppkey

Representative DataFrame

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

def q15_expression_impl(ctx: DataFrameContext) -> Any:
    supplier = ctx.get_table("supplier")
    lineitem = ctx.get_table("lineitem")
    col = ctx.col
    lit = ctx.lit

    params = get_tpch_parameters(15)
    start_date = params["start_date"]
    end_date = params["end_date"]

    revenue = (
        lineitem.filter((col("l_shipdate") >= lit(start_date)) & (col("l_shipdate") < lit(end_date)))
        .group_by(col("l_suppkey").alias("supplier_no"))
        .agg((col("l_extendedprice") * (lit(1) - col("l_discount"))).sum().alias("total_revenue"))
    )

    max_revenue = ctx.scalar(revenue.select(col("total_revenue").max().alias("max_rev")))

    tolerance = abs(max_revenue) * 1e-9 if max_revenue else 1e-9
    return (
        supplier.join(revenue, left_on="s_suppkey", right_on="supplier_no")
        .filter((col("total_revenue") - lit(max_revenue)).abs() <= lit(tolerance))
        .select("s_suppkey", "s_name", "s_address", "s_phone", "total_revenue")
        .sort("s_suppkey")
    )

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 TPCHSkew

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

Parameter substitution for this benchmark is seed-driven; pass a seed for a specific stream’s parameters:

print(bench.get_query(15, scale_factor=0.01, seed=42, dialect="datafusion"))

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('tpch_skew', '15', family="expression").source)  # or family="pandas"

Source

Query text: benchbox/_binaries/tpc-h/templates/queries/15.sql