github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query35.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    ca_state,
    19    cd_gender,
    20    cd_marital_status,
    21    cd_dep_count,
    22    count(*) cnt1,
    23    avg(cd_dep_count),
    24    max(cd_dep_count),
    25    sum(cd_dep_count),
    26    cd_dep_employed_count,
    27    count(*) cnt2,
    28    avg(cd_dep_employed_count),
    29    max(cd_dep_employed_count),
    30    sum(cd_dep_employed_count),
    31    cd_dep_college_count,
    32    count(*) cnt3,
    33    avg(cd_dep_college_count),
    34    max(cd_dep_college_count),
    35    sum(cd_dep_college_count)
    36   from
    37    customer c,customer_address ca,customer_demographics
    38   where
    39    c.c_current_addr_sk = ca.ca_address_sk and
    40    cd_demo_sk = c.c_current_cdemo_sk and 
    41    exists (select *
    42            from store_sales,date_dim
    43            where c.c_customer_sk = ss_customer_sk and
    44                  ss_sold_date_sk = d_date_sk and
    45                  d_year = 1999 and
    46                  d_qoy < 4) and
    47     (exists (select *
    48              from web_sales,date_dim
    49              where c.c_customer_sk = ws_bill_customer_sk and
    50                    ws_sold_date_sk = d_date_sk and
    51                    d_year = 1999 and
    52                    d_qoy < 4) or 
    53      exists (select * 
    54              from catalog_sales,date_dim
    55              where c.c_customer_sk = cs_ship_customer_sk and
    56                    cs_sold_date_sk = d_date_sk and
    57                    d_year = 1999 and
    58                    d_qoy < 4))
    59   group by ca_state,
    60            cd_gender,
    61            cd_marital_status,
    62            cd_dep_count,
    63            cd_dep_employed_count,
    64            cd_dep_college_count
    65   order by ca_state,
    66            cd_gender,
    67            cd_marital_status,
    68            cd_dep_count,
    69            cd_dep_employed_count,
    70            cd_dep_college_count
    71   limit 100