TPC-DS-OBT Q59

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

Representative SQL

Rendered for DataFusion with default parameters.

WITH wss AS (
  SELECT
    obt.sold_date_d_week_seq,
    obt.store_sk,
    obt.store_s_store_name,
    obt.store_s_store_id,
    obt.sold_date_d_month_seq,
    SUM(
      CASE
        WHEN (
          obt.sold_date_d_day_name = 'Sunday'
        )
        THEN obt.sales_price
        ELSE NULL
      END
    ) AS sun_sales,
    SUM(
      CASE
        WHEN (
          obt.sold_date_d_day_name = 'Monday'
        )
        THEN obt.sales_price
        ELSE NULL
      END
    ) AS mon_sales,
    SUM(
      CASE
        WHEN (
          obt.sold_date_d_day_name = 'Tuesday'
        )
        THEN obt.sales_price
        ELSE NULL
      END
    ) AS tue_sales,
    SUM(
      CASE
        WHEN (
          obt.sold_date_d_day_name = 'Wednesday'
        )
        THEN obt.sales_price
        ELSE NULL
      END
    ) AS wed_sales,
    SUM(
      CASE
        WHEN (
          obt.sold_date_d_day_name = 'Thursday'
        )
        THEN obt.sales_price
        ELSE NULL
      END
    ) AS thu_sales,
    SUM(
      CASE
        WHEN (
          obt.sold_date_d_day_name = 'Friday'
        )
        THEN obt.sales_price
        ELSE NULL
      END
    ) AS fri_sales,
    SUM(
      CASE
        WHEN (
          obt.sold_date_d_day_name = 'Saturday'
        )
        THEN obt.sales_price
        ELSE NULL
      END
    ) AS sat_sales
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    obt.sold_date_d_date_sk = obt.sold_date_sk AND channel = 'store'
  GROUP BY
    obt.sold_date_d_week_seq,
    obt.store_sk,
    obt.store_s_store_name,
    obt.store_s_store_id,
    obt.sold_date_d_month_seq
)
SELECT
  s_store_name1 AS store_s_store_name,
  s_store_id1 AS store_s_store_id,
  d_week_seq1,
  CAST(sun_sales1 AS DOUBLE PRECISION) / sun_sales2,
  CAST(mon_sales1 AS DOUBLE PRECISION) / mon_sales2,
  CAST(tue_sales1 AS DOUBLE PRECISION) / tue_sales2,
  CAST(wed_sales1 AS DOUBLE PRECISION) / wed_sales2,
  CAST(thu_sales1 AS DOUBLE PRECISION) / thu_sales2,
  CAST(fri_sales1 AS DOUBLE PRECISION) / fri_sales2,
  CAST(sat_sales1 AS DOUBLE PRECISION) / sat_sales2
FROM (
  SELECT
    wss.store_s_store_name AS s_store_name1,
    wss.sold_date_d_week_seq AS d_week_seq1,
    wss.store_s_store_id AS s_store_id1,
    sun_sales AS sun_sales1,
    mon_sales AS mon_sales1,
    tue_sales AS tue_sales1,
    wed_sales AS wed_sales1,
    thu_sales AS thu_sales1,
    fri_sales AS fri_sales1,
    sat_sales AS sat_sales1
  FROM wss
  WHERE
    wss.sold_date_d_month_seq BETWEEN 1176 AND 1176 + 11
) AS y, (
  SELECT
    wss.store_s_store_name AS s_store_name2,
    wss.sold_date_d_week_seq AS d_week_seq2,
    wss.store_s_store_id AS s_store_id2,
    sun_sales AS sun_sales2,
    mon_sales AS mon_sales2,
    tue_sales AS tue_sales2,
    wed_sales AS wed_sales2,
    thu_sales AS thu_sales2,
    fri_sales AS fri_sales2,
    sat_sales AS sat_sales2
  FROM wss
  WHERE
    wss.sold_date_d_month_seq BETWEEN 1176 + 12 AND 1176 + 23
) AS x
WHERE
  s_store_id1 = s_store_id2 AND d_week_seq1 = d_week_seq2 - 52
ORDER BY
  store_s_store_name,
  store_s_store_id,
  d_week_seq1
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['59'])

Source

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