| -- 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 |
| cc_call_center_id Call_Center, |
| cc_name Call_Center_Name, |
| cc_manager Manager, |
| sum(cr_net_loss) Returns_Loss |
| from |
| call_center, |
| catalog_returns, |
| date_dim, |
| customer, |
| customer_address, |
| customer_demographics, |
| household_demographics |
| where |
| cr_call_center_sk = cc_call_center_sk |
| and cr_returned_date_sk = d_date_sk |
| and cr_returning_customer_sk= c_customer_sk |
| and cd_demo_sk = c_current_cdemo_sk |
| and hd_demo_sk = c_current_hdemo_sk |
| and ca_address_sk = c_current_addr_sk |
| and d_year = 1999 |
| and d_moy = 11 |
| and ( (cd_marital_status = 'M' and cd_education_status = 'Unknown') |
| or(cd_marital_status = 'W' and cd_education_status = 'Advanced Degree')) |
| and hd_buy_potential like '0-500%' |
| and ca_gmt_offset = -7 |
| group by cc_call_center_id,cc_name,cc_manager,cd_marital_status,cd_education_status |
| order by sum(cr_net_loss) desc |
