resources/tpcds-v2.7.0/q72.sql

0001 SELECT
0002   i_item_desc,
0003   w_warehouse_name,
0004   d1.d_week_seq,
0005   count(CASE WHEN p_promo_sk IS NULL
0006     THEN 1
0007         ELSE 0 END) no_promo,
0008   count(CASE WHEN p_promo_sk IS NOT NULL
0009     THEN 1
0010         ELSE 0 END) promo,
0011   count(*) total_cnt
0012 FROM catalog_sales
0013   JOIN inventory ON (cs_item_sk = inv_item_sk)
0014   JOIN warehouse ON (w_warehouse_sk = inv_warehouse_sk)
0015   JOIN item ON (i_item_sk = cs_item_sk)
0016   JOIN customer_demographics ON (cs_bill_cdemo_sk = cd_demo_sk)
0017   JOIN household_demographics ON (cs_bill_hdemo_sk = hd_demo_sk)
0018   JOIN date_dim d1 ON (cs_sold_date_sk = d1.d_date_sk)
0019   JOIN date_dim d2 ON (inv_date_sk = d2.d_date_sk)
0020   JOIN date_dim d3 ON (cs_ship_date_sk = d3.d_date_sk)
0021   LEFT OUTER JOIN promotion ON (cs_promo_sk = p_promo_sk)
0022   LEFT OUTER JOIN catalog_returns ON (cr_item_sk = cs_item_sk AND cr_order_number = cs_order_number)
0023 -- q72 in TPCDS v1.4 had conditions below:
0024 -- WHERE d1.d_week_seq = d2.d_week_seq
0025 --   AND inv_quantity_on_hand < cs_quantity
0026 --   AND d3.d_date > (cast(d1.d_date AS DATE) + interval 5 days)
0027 --   AND hd_buy_potential = '>10000'
0028 --   AND d1.d_year = 1999
0029 --   AND hd_buy_potential = '>10000'
0030 --   AND cd_marital_status = 'D'
0031 --   AND d1.d_year = 1999
0032 WHERE d1.d_week_seq = d2.d_week_seq
0033     AND inv_quantity_on_hand < cs_quantity
0034     AND d3.d_date > d1.d_date + INTERVAL 5 days
0035     AND hd_buy_potential = '1001-5000'
0036     AND d1.d_year = 2001
0037     AND cd_marital_status = 'M'
0038 GROUP BY i_item_desc, w_warehouse_name, d1.d_week_seq
0039 ORDER BY total_cnt DESC, i_item_desc, w_warehouse_name, d_week_seq
0040 LIMIT 100