0001 WITH cs_ui AS
0002 (SELECT
0003 cs_item_sk,
0004 sum(cs_ext_list_price) AS sale,
0005 sum(cr_refunded_cash + cr_reversed_charge + cr_store_credit) AS refund
0006 FROM catalog_sales
0007 , catalog_returns
0008 WHERE cs_item_sk = cr_item_sk
0009 AND cs_order_number = cr_order_number
0010 GROUP BY cs_item_sk
0011 HAVING sum(cs_ext_list_price) > 2 * sum(cr_refunded_cash + cr_reversed_charge + cr_store_credit)),
0012 cross_sales AS
0013 (SELECT
0014 i_product_name product_name,
0015 i_item_sk item_sk,
0016 s_store_name store_name,
0017 s_zip store_zip,
0018 ad1.ca_street_number b_street_number,
0019 ad1.ca_street_name b_streen_name,
0020 ad1.ca_city b_city,
0021 ad1.ca_zip b_zip,
0022 ad2.ca_street_number c_street_number,
0023 ad2.ca_street_name c_street_name,
0024 ad2.ca_city c_city,
0025 ad2.ca_zip c_zip,
0026 d1.d_year AS syear,
0027 d2.d_year AS fsyear,
0028 d3.d_year s2year,
0029 count(*) cnt,
0030 sum(ss_wholesale_cost) s1,
0031 sum(ss_list_price) s2,
0032 sum(ss_coupon_amt) s3
0033 FROM store_sales, store_returns, cs_ui, date_dim d1, date_dim d2, date_dim d3,
0034 store, customer, customer_demographics cd1, customer_demographics cd2,
0035 promotion, household_demographics hd1, household_demographics hd2,
0036 customer_address ad1, customer_address ad2, income_band ib1, income_band ib2, item
0037 WHERE ss_store_sk = s_store_sk AND
0038 ss_sold_date_sk = d1.d_date_sk AND
0039 ss_customer_sk = c_customer_sk AND
0040 ss_cdemo_sk = cd1.cd_demo_sk AND
0041 ss_hdemo_sk = hd1.hd_demo_sk AND
0042 ss_addr_sk = ad1.ca_address_sk AND
0043 ss_item_sk = i_item_sk AND
0044 ss_item_sk = sr_item_sk AND
0045 ss_ticket_number = sr_ticket_number AND
0046 ss_item_sk = cs_ui.cs_item_sk AND
0047 c_current_cdemo_sk = cd2.cd_demo_sk AND
0048 c_current_hdemo_sk = hd2.hd_demo_sk AND
0049 c_current_addr_sk = ad2.ca_address_sk AND
0050 c_first_sales_date_sk = d2.d_date_sk AND
0051 c_first_shipto_date_sk = d3.d_date_sk AND
0052 ss_promo_sk = p_promo_sk AND
0053 hd1.hd_income_band_sk = ib1.ib_income_band_sk AND
0054 hd2.hd_income_band_sk = ib2.ib_income_band_sk AND
0055 cd1.cd_marital_status <> cd2.cd_marital_status AND
0056 i_color IN ('purple', 'burlywood', 'indian', 'spring', 'floral', 'medium') AND
0057 i_current_price BETWEEN 64 AND 64 + 10 AND
0058 i_current_price BETWEEN 64 + 1 AND 64 + 15
0059 GROUP BY
0060 i_product_name,
0061 i_item_sk,
0062 s_store_name,
0063 s_zip,
0064 ad1.ca_street_number,
0065 ad1.ca_street_name,
0066 ad1.ca_city,
0067 ad1.ca_zip,
0068 ad2.ca_street_number,
0069 ad2.ca_street_name,
0070 ad2.ca_city,
0071 ad2.ca_zip,
0072 d1.d_year,
0073 d2.d_year,
0074 d3.d_year
0075 )
0076 SELECT
0077 cs1.product_name,
0078 cs1.store_name,
0079 cs1.store_zip,
0080 cs1.b_street_number,
0081 cs1.b_streen_name,
0082 cs1.b_city,
0083 cs1.b_zip,
0084 cs1.c_street_number,
0085 cs1.c_street_name,
0086 cs1.c_city,
0087 cs1.c_zip,
0088 cs1.syear,
0089 cs1.cnt,
0090 cs1.s1,
0091 cs1.s2,
0092 cs1.s3,
0093 cs2.s1,
0094 cs2.s2,
0095 cs2.s3,
0096 cs2.syear,
0097 cs2.cnt
0098 FROM cross_sales cs1, cross_sales cs2
0099 WHERE cs1.item_sk = cs2.item_sk AND
0100 cs1.syear = 1999 AND
0101 cs2.syear = 1999 + 1 AND
0102 cs2.cnt <= cs1.cnt AND
0103 cs1.store_name = cs2.store_name AND
0104 cs1.store_zip = cs2.store_zip
0105 ORDER BY
0106 cs1.product_name,
0107 cs1.store_name,
0108 cs2.cnt,
0109
0110 cs1.s1,
0111 cs2.s1