github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query85.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  substring(r_reason_desc,1,20)
    18         ,avg(ws_quantity)
    19         ,avg(wr_refunded_cash)
    20         ,avg(wr_fee)
    21   from web_sales, web_returns, web_page, customer_demographics cd1,
    22        customer_demographics cd2, customer_address, date_dim, reason 
    23   where ws_web_page_sk = wp_web_page_sk
    24     and ws_item_sk = wr_item_sk
    25     and ws_order_number = wr_order_number
    26     and ws_sold_date_sk = d_date_sk and d_year = 1998
    27     and cd1.cd_demo_sk = wr_refunded_cdemo_sk 
    28     and cd2.cd_demo_sk = wr_returning_cdemo_sk
    29     and ca_address_sk = wr_refunded_addr_sk
    30     and r_reason_sk = wr_reason_sk
    31     and
    32     (
    33      (
    34       cd1.cd_marital_status = 'M'
    35       and
    36       cd1.cd_marital_status = cd2.cd_marital_status
    37       and
    38       cd1.cd_education_status = '4 yr Degree'
    39       and 
    40       cd1.cd_education_status = cd2.cd_education_status
    41       and
    42       ws_sales_price between 100.00 and 150.00
    43      )
    44     or
    45      (
    46       cd1.cd_marital_status = 'D'
    47       and
    48       cd1.cd_marital_status = cd2.cd_marital_status
    49       and
    50       cd1.cd_education_status = 'Primary' 
    51       and
    52       cd1.cd_education_status = cd2.cd_education_status
    53       and
    54       ws_sales_price between 50.00 and 100.00
    55      )
    56     or
    57      (
    58       cd1.cd_marital_status = 'U'
    59       and
    60       cd1.cd_marital_status = cd2.cd_marital_status
    61       and
    62       cd1.cd_education_status = 'Advanced Degree'
    63       and
    64       cd1.cd_education_status = cd2.cd_education_status
    65       and
    66       ws_sales_price between 150.00 and 200.00
    67      )
    68     )
    69     and
    70     (
    71      (
    72       ca_country = 'United States'
    73       and
    74       ca_state in ('KY', 'GA', 'NM')
    75       and ws_net_profit between 100 and 200  
    76      )
    77      or
    78      (
    79       ca_country = 'United States'
    80       and
    81       ca_state in ('MT', 'OR', 'IN')
    82       and ws_net_profit between 150 and 300  
    83      )
    84      or
    85      (
    86       ca_country = 'United States'
    87       and
    88       ca_state in ('WI', 'MO', 'WV')
    89       and ws_net_profit between 50 and 250  
    90      )
    91     )
    92  group by r_reason_desc
    93  order by substring(r_reason_desc,1,20)
    94          ,avg(ws_quantity)
    95          ,avg(wr_refunded_cash)
    96          ,avg(wr_fee)
    97  limit 100