TPC-DS-OBT Q53

TPC-DS-OBT benchmark · all TPC-DS-OBT queries

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  *
FROM (
  SELECT
    obt.item_i_manufact_id,
    SUM(obt.sales_price) AS sum_sales,
    AVG(SUM(obt.sales_price)) OVER (PARTITION BY obt.item_i_manufact_id) AS avg_quarterly_sales
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.item_sk = obt.item_i_item_sk
      AND obt.sold_date_sk = obt.sold_date_d_date_sk
      AND obt.store_sk = obt.store_s_store_sk
      AND obt.sold_date_d_month_seq IN (
        1176,
        1176 + 1,
        1176 + 2,
        1176 + 3,
        1176 + 4,
        1176 + 5,
        1176 + 6,
        1176 + 7,
        1176 + 8,
        1176 + 9,
        1176 + 10,
        1176 + 11
      )
      AND (
        (
          obt.item_i_category IN ('Books', 'Children', 'Electronics')
          AND obt.item_i_class IN ('personal', 'portable', 'reference', 'self-help')
          AND obt.item_i_brand IN (
            'scholaramalgamalg #14',
            'scholaramalgamalg #7',
            'exportiunivamalg #9',
            'scholaramalgamalg #9'
          )
        )
        OR (
          obt.item_i_category IN ('Women', 'Music', 'Men')
          AND obt.item_i_class IN ('accessories', 'classical', 'fragrances', 'pants')
          AND obt.item_i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1')
        )
      )
    )
    AND channel = 'store'
  GROUP BY
    obt.item_i_manufact_id,
    obt.sold_date_d_qoy
) AS tmp1
WHERE
  CASE
    WHEN avg_quarterly_sales > 0
    THEN CAST(ABS(sum_sales - avg_quarterly_sales) AS DOUBLE PRECISION) / avg_quarterly_sales
    ELSE NULL
  END > 0.1
ORDER BY
  avg_quarterly_sales,
  sum_sales,
  item_i_manufact_id
LIMIT 100

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 TPCDSOBT

bench = TPCDSOBT(scale_factor=1.0)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries['53'])

Source

Query text: benchbox/core/tpcds_obt/queries.py