| /* |
| * 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 = 1998 |
| 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 'Unknown%' |
| 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; |
