github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query91.sql (about)

     1  -- Licensed to the Apache Software Foundation (ASF) under one
     2  -- or more contributor license agreements.  See the NOTICE file
     3  -- distributed with this work for additional information
     4  -- regarding copyright ownership.  The ASF licenses this file
     5  -- to you under the Apache License, Version 2.0 (the
     6  -- "License"); you may not use this file except in compliance
     7  -- with the License.  You may obtain a copy of the License at
     8  --
     9  --     http://www.apache.org/licenses/LICENSE-2.0
    10  --
    11  -- Unless required by applicable law or agreed to in writing, software
    12  -- distributed under the License is distributed on an "AS IS" BASIS,
    13  -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    14  -- See the License for the specific language governing permissions and
    15  -- limitations under the License.
    16  
    17  select
    18          cc_call_center_id Call_Center,
    19          cc_name Call_Center_Name,
    20          cc_manager Manager,
    21          sum(cr_net_loss) Returns_Loss
    22  from
    23          call_center,
    24          catalog_returns,
    25          date_dim,
    26          customer,
    27          customer_address,
    28          customer_demographics,
    29          household_demographics
    30  where
    31          cr_call_center_sk       = cc_call_center_sk
    32  and     cr_returned_date_sk     = d_date_sk
    33  and     cr_returning_customer_sk= c_customer_sk
    34  and     cd_demo_sk              = c_current_cdemo_sk
    35  and     hd_demo_sk              = c_current_hdemo_sk
    36  and     ca_address_sk           = c_current_addr_sk
    37  and     d_year                  = 1999 
    38  and     d_moy                   = 11
    39  and     ( (cd_marital_status       = 'M' and cd_education_status     = 'Unknown')
    40          or(cd_marital_status       = 'W' and cd_education_status     = 'Advanced Degree'))
    41  and     hd_buy_potential like '0-500%'
    42  and     ca_gmt_offset           = -7
    43  group by cc_call_center_id,cc_name,cc_manager,cd_marital_status,cd_education_status
    44  order by sum(cr_net_loss) desc