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 WHERE d1.d_week_seq = d2.d_week_seq
0024 AND inv_quantity_on_hand < cs_quantity
0025 AND d3.d_date > (cast(d1.d_date AS DATE) + interval 5 days)
0026 AND hd_buy_potential = '>10000'
0027 AND d1.d_year = 1999
0028 AND hd_buy_potential = '>10000'
0029 AND cd_marital_status = 'D'
0030 AND d1.d_year = 1999
0031 GROUP BY i_item_desc, w_warehouse_name, d1.d_week_seq
0032 ORDER BY total_cnt DESC, i_item_desc, w_warehouse_name, d_week_seq
0033 LIMIT 100
