0001 SELECT
0002 substr(r_reason_desc, 1, 20),
0003 avg(ws_quantity),
0004 avg(wr_refunded_cash),
0005 avg(wr_fee)
0006 FROM web_sales, web_returns, web_page, customer_demographics cd1,
0007 customer_demographics cd2, customer_address, date_dim, reason
0008 WHERE ws_web_page_sk = wp_web_page_sk
0009 AND ws_item_sk = wr_item_sk
0010 AND ws_order_number = wr_order_number
0011 AND ws_sold_date_sk = d_date_sk AND d_year = 2000
0012 AND cd1.cd_demo_sk = wr_refunded_cdemo_sk
0013 AND cd2.cd_demo_sk = wr_returning_cdemo_sk
0014 AND ca_address_sk = wr_refunded_addr_sk
0015 AND r_reason_sk = wr_reason_sk
0016 AND
0017 (
0018 (
0019 cd1.cd_marital_status = 'M'
0020 AND
0021 cd1.cd_marital_status = cd2.cd_marital_status
0022 AND
0023 cd1.cd_education_status = 'Advanced Degree'
0024 AND
0025 cd1.cd_education_status = cd2.cd_education_status
0026 AND
0027 ws_sales_price BETWEEN 100.00 AND 150.00
0028 )
0029 OR
0030 (
0031 cd1.cd_marital_status = 'S'
0032 AND
0033 cd1.cd_marital_status = cd2.cd_marital_status
0034 AND
0035 cd1.cd_education_status = 'College'
0036 AND
0037 cd1.cd_education_status = cd2.cd_education_status
0038 AND
0039 ws_sales_price BETWEEN 50.00 AND 100.00
0040 )
0041 OR
0042 (
0043 cd1.cd_marital_status = 'W'
0044 AND
0045 cd1.cd_marital_status = cd2.cd_marital_status
0046 AND
0047 cd1.cd_education_status = '2 yr Degree'
0048 AND
0049 cd1.cd_education_status = cd2.cd_education_status
0050 AND
0051 ws_sales_price BETWEEN 150.00 AND 200.00
0052 )
0053 )
0054 AND
0055 (
0056 (
0057 ca_country = 'United States'
0058 AND
0059 ca_state IN ('IN', 'OH', 'NJ')
0060 AND ws_net_profit BETWEEN 100 AND 200
0061 )
0062 OR
0063 (
0064 ca_country = 'United States'
0065 AND
0066 ca_state IN ('WI', 'CT', 'KY')
0067 AND ws_net_profit BETWEEN 150 AND 300
0068 )
0069 OR
0070 (
0071 ca_country = 'United States'
0072 AND
0073 ca_state IN ('LA', 'IA', 'AR')
0074 AND ws_net_profit BETWEEN 50 AND 250
0075 )
0076 )
0077 GROUP BY r_reason_desc
0078 ORDER BY substr(r_reason_desc, 1, 20)
0079 , avg(ws_quantity)
0080 , avg(wr_refunded_cash)
0081 , avg(wr_fee)
0082 LIMIT 100
