| -- Licensed to the Apache Software Foundation (ASF) under one |
| -- or more contributor license agreements. See the NOTICE file |
| -- distributed with this work for additional information |
| -- regarding copyright ownership. The ASF licenses this file |
| -- to you under the Apache License, Version 2.0 (the |
| -- "License"); you may not use this file except in compliance |
| -- with the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, software |
| -- distributed under the License is distributed on an "AS IS" BASIS, |
| -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| -- See the License for the specific language governing permissions and |
| -- limitations under the License. |
| |
| select substr(r_reason_desc,1,20) |
| ,avg(ws_quantity) |
| ,avg(wr_refunded_cash) |
| ,avg(wr_fee) |
| from web_sales, web_returns, web_page, customer_demographics cd1, |
| customer_demographics cd2, customer_address, date_dim, reason |
| where ws_web_page_sk = wp_web_page_sk |
| and ws_item_sk = wr_item_sk |
| and ws_order_number = wr_order_number |
| and ws_sold_date_sk = d_date_sk and d_year = 1998 |
| and cd1.cd_demo_sk = wr_refunded_cdemo_sk |
| and cd2.cd_demo_sk = wr_returning_cdemo_sk |
| and ca_address_sk = wr_refunded_addr_sk |
| and r_reason_sk = wr_reason_sk |
| and |
| ( |
| ( |
| cd1.cd_marital_status = 'M' |
| and |
| cd1.cd_marital_status = cd2.cd_marital_status |
| and |
| cd1.cd_education_status = '4 yr Degree' |
| and |
| cd1.cd_education_status = cd2.cd_education_status |
| and |
| ws_sales_price between 100.00 and 150.00 |
| ) |
| or |
| ( |
| cd1.cd_marital_status = 'D' |
| and |
| cd1.cd_marital_status = cd2.cd_marital_status |
| and |
| cd1.cd_education_status = 'Primary' |
| and |
| cd1.cd_education_status = cd2.cd_education_status |
| and |
| ws_sales_price between 50.00 and 100.00 |
| ) |
| or |
| ( |
| cd1.cd_marital_status = 'U' |
| and |
| cd1.cd_marital_status = cd2.cd_marital_status |
| and |
| cd1.cd_education_status = 'Advanced Degree' |
| and |
| cd1.cd_education_status = cd2.cd_education_status |
| and |
| ws_sales_price between 150.00 and 200.00 |
| ) |
| ) |
| and |
| ( |
| ( |
| ca_country = 'United States' |
| and |
| ca_state in ('KY', 'GA', 'NM') |
| and ws_net_profit between 100 and 200 |
| ) |
| or |
| ( |
| ca_country = 'United States' |
| and |
| ca_state in ('MT', 'OR', 'IN') |
| and ws_net_profit between 150 and 300 |
| ) |
| or |
| ( |
| ca_country = 'United States' |
| and |
| ca_state in ('WI', 'MO', 'WV') |
| and ws_net_profit between 50 and 250 |
| ) |
| ) |
| group by r_reason_desc |
| order by substr(r_reason_desc,1,20) |
| ,avg(ws_quantity) |
| ,avg(wr_refunded_cash) |
| ,avg(wr_fee) |
| limit 100 |