TPC-DS-OBT Q47

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

Representative SQL

Rendered for DataFusion with default parameters.

WITH v1 AS (
  SELECT
    obt.item_i_category AS i_category,
    obt.item_i_brand AS i_brand,
    obt.store_s_store_name AS s_store_name,
    obt.store_s_company_name AS s_company_name,
    obt.sold_date_d_year AS d_year,
    obt.sold_date_d_moy AS d_moy,
    SUM(obt.sales_price) AS sum_sales,
    AVG(SUM(obt.sales_price)) OVER (
      PARTITION BY obt.item_i_category, obt.item_i_brand, obt.store_s_store_name, obt.store_s_company_name, obt.sold_date_d_year
    ) AS avg_monthly_sales,
    RANK() OVER (
      PARTITION BY obt.item_i_category, obt.item_i_brand, obt.store_s_store_name, obt.store_s_company_name
      ORDER BY obt.sold_date_d_year, obt.sold_date_d_moy
    ) AS rn
  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_year = 1999
        OR (
          obt.sold_date_d_year = 1999 - 1 AND obt.sold_date_d_moy = 12
        )
        OR (
          obt.sold_date_d_year = 1999 + 1 AND obt.sold_date_d_moy = 1
        )
      )
    )
    AND channel = 'store'
  GROUP BY
    obt.item_i_category,
    obt.item_i_brand,
    obt.store_s_store_name,
    obt.store_s_company_name,
    obt.sold_date_d_year,
    obt.sold_date_d_moy
), v2 AS (
  SELECT
    v1.i_category,
    v1.d_year,
    v1.avg_monthly_sales,
    v1.sum_sales,
    v1_lag.sum_sales AS psum,
    v1_lead.sum_sales AS nsum
  FROM v1, v1 AS v1_lag, v1 AS v1_lead
  WHERE
    v1.i_category = v1_lag.i_category
    AND v1.i_category = v1_lead.i_category
    AND v1.i_brand = v1_lag.i_brand
    AND v1.i_brand = v1_lead.i_brand
    AND v1.s_store_name = v1_lag.s_store_name
    AND v1.s_store_name = v1_lead.s_store_name
    AND v1.s_company_name = v1_lag.s_company_name
    AND v1.s_company_name = v1_lead.s_company_name
    AND v1.rn = v1_lag.rn + 1
    AND v1.rn = v1_lead.rn - 1
)
SELECT
  *
FROM v2
WHERE
  d_year = 1999
  AND avg_monthly_sales > 0
  AND CASE
    WHEN avg_monthly_sales > 0
    THEN CAST(ABS(sum_sales - avg_monthly_sales) AS DOUBLE PRECISION) / avg_monthly_sales
    ELSE NULL
  END > 0.1
ORDER BY
  sum_sales - avg_monthly_sales,
  avg_monthly_sales
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['47'])

Source

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