github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query11.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  with year_total as (
    18   select c_customer_id customer_id
    19         ,c_first_name customer_first_name
    20         ,c_last_name customer_last_name
    21         ,c_preferred_cust_flag customer_preferred_cust_flag
    22         ,c_birth_country customer_birth_country
    23         ,c_login customer_login
    24         ,c_email_address customer_email_address
    25         ,d_year dyear
    26         ,sum(ss_ext_list_price-ss_ext_discount_amt) year_total
    27         ,'s' sale_type
    28   from customer
    29       ,store_sales
    30       ,date_dim
    31   where c_customer_sk = ss_customer_sk
    32     and ss_sold_date_sk = d_date_sk
    33   group by c_customer_id
    34           ,c_first_name
    35           ,c_last_name
    36           ,c_preferred_cust_flag 
    37           ,c_birth_country
    38           ,c_login
    39           ,c_email_address
    40           ,d_year 
    41   union all
    42   select c_customer_id customer_id
    43         ,c_first_name customer_first_name
    44         ,c_last_name customer_last_name
    45         ,c_preferred_cust_flag customer_preferred_cust_flag
    46         ,c_birth_country customer_birth_country
    47         ,c_login customer_login
    48         ,c_email_address customer_email_address
    49         ,d_year dyear
    50         ,sum(ws_ext_list_price-ws_ext_discount_amt) year_total
    51         ,'w' sale_type
    52   from customer
    53       ,web_sales
    54       ,date_dim
    55   where c_customer_sk = ws_bill_customer_sk
    56     and ws_sold_date_sk = d_date_sk
    57   group by c_customer_id
    58           ,c_first_name
    59           ,c_last_name
    60           ,c_preferred_cust_flag 
    61           ,c_birth_country
    62           ,c_login
    63           ,c_email_address
    64           ,d_year
    65           )
    66    select  
    67                    t_s_secyear.customer_id
    68                   ,t_s_secyear.customer_first_name
    69                   ,t_s_secyear.customer_last_name
    70                   ,t_s_secyear.customer_email_address
    71   from year_total t_s_firstyear
    72       ,year_total t_s_secyear
    73       ,year_total t_w_firstyear
    74       ,year_total t_w_secyear
    75   where t_s_secyear.customer_id = t_s_firstyear.customer_id
    76           and t_s_firstyear.customer_id = t_w_secyear.customer_id
    77           and t_s_firstyear.customer_id = t_w_firstyear.customer_id
    78           and t_s_firstyear.sale_type = 's'
    79           and t_w_firstyear.sale_type = 'w'
    80           and t_s_secyear.sale_type = 's'
    81           and t_w_secyear.sale_type = 'w'
    82           and t_s_firstyear.dyear = 2001
    83           and t_s_secyear.dyear = 2001+1
    84           and t_w_firstyear.dyear = 2001
    85           and t_w_secyear.dyear = 2001+1
    86           and t_s_firstyear.year_total > 0
    87           and t_w_firstyear.year_total > 0
    88           and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else 0.0 end
    89               > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else 0.0 end
    90   order by t_s_secyear.customer_id
    91           ,t_s_secyear.customer_first_name
    92           ,t_s_secyear.customer_last_name
    93           ,t_s_secyear.customer_email_address
    94  limit 100