github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query69.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    cd_gender,
    19    cd_marital_status,
    20    cd_education_status,
    21    count(*) cnt1,
    22    cd_purchase_estimate,
    23    count(*) cnt2,
    24    cd_credit_rating,
    25    count(*) cnt3
    26   from
    27    customer c,customer_address ca,customer_demographics
    28   where
    29    c.c_current_addr_sk = ca.ca_address_sk and
    30    ca_state in ('CO','IL','MN') and
    31    cd_demo_sk = c.c_current_cdemo_sk and 
    32    exists (select *
    33            from store_sales,date_dim
    34            where c.c_customer_sk = ss_customer_sk and
    35                  ss_sold_date_sk = d_date_sk and
    36                  d_year = 1999 and
    37                  d_moy between 1 and 1+2) and
    38     (not exists (select *
    39              from web_sales,date_dim
    40              where c.c_customer_sk = ws_bill_customer_sk and
    41                    ws_sold_date_sk = d_date_sk and
    42                    d_year = 1999 and
    43                    d_moy between 1 and 1+2) and
    44      not exists (select * 
    45              from catalog_sales,date_dim
    46              where c.c_customer_sk = cs_ship_customer_sk and
    47                    cs_sold_date_sk = d_date_sk and
    48                    d_year = 1999 and
    49                    d_moy between 1 and 1+2))
    50   group by cd_gender,
    51            cd_marital_status,
    52            cd_education_status,
    53            cd_purchase_estimate,
    54            cd_credit_rating
    55   order by cd_gender,
    56            cd_marital_status,
    57            cd_education_status,
    58            cd_purchase_estimate,
    59            cd_credit_rating
    60   limit 100