github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query4.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_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) 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((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2) ) year_total 51 ,'c' sale_type 52 from customer 53 ,catalog_sales 54 ,date_dim 55 where c_customer_sk = cs_bill_customer_sk 56 and cs_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 union all 66 select c_customer_id customer_id 67 ,c_first_name customer_first_name 68 ,c_last_name customer_last_name 69 ,c_preferred_cust_flag customer_preferred_cust_flag 70 ,c_birth_country customer_birth_country 71 ,c_login customer_login 72 ,c_email_address customer_email_address 73 ,d_year dyear 74 ,sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2) ) year_total 75 ,'w' sale_type 76 from customer 77 ,web_sales 78 ,date_dim 79 where c_customer_sk = ws_bill_customer_sk 80 and ws_sold_date_sk = d_date_sk 81 group by c_customer_id 82 ,c_first_name 83 ,c_last_name 84 ,c_preferred_cust_flag 85 ,c_birth_country 86 ,c_login 87 ,c_email_address 88 ,d_year 89 ) 90 select 91 t_s_secyear.customer_id 92 ,t_s_secyear.customer_first_name 93 ,t_s_secyear.customer_last_name 94 ,t_s_secyear.customer_email_address 95 from year_total t_s_firstyear 96 ,year_total t_s_secyear 97 ,year_total t_c_firstyear 98 ,year_total t_c_secyear 99 ,year_total t_w_firstyear 100 ,year_total t_w_secyear 101 where t_s_secyear.customer_id = t_s_firstyear.customer_id 102 and t_s_firstyear.customer_id = t_c_secyear.customer_id 103 and t_s_firstyear.customer_id = t_c_firstyear.customer_id 104 and t_s_firstyear.customer_id = t_w_firstyear.customer_id 105 and t_s_firstyear.customer_id = t_w_secyear.customer_id 106 and t_s_firstyear.sale_type = 's' 107 and t_c_firstyear.sale_type = 'c' 108 and t_w_firstyear.sale_type = 'w' 109 and t_s_secyear.sale_type = 's' 110 and t_c_secyear.sale_type = 'c' 111 and t_w_secyear.sale_type = 'w' 112 and t_s_firstyear.dyear = 2001 113 and t_s_secyear.dyear = 2001+1 114 and t_c_firstyear.dyear = 2001 115 and t_c_secyear.dyear = 2001+1 116 and t_w_firstyear.dyear = 2001 117 and t_w_secyear.dyear = 2001+1 118 and t_s_firstyear.year_total > 0 119 and t_c_firstyear.year_total > 0 120 and t_w_firstyear.year_total > 0 121 and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end 122 > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end 123 and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end 124 > case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end 125 order by t_s_secyear.customer_id 126 ,t_s_secyear.customer_first_name 127 ,t_s_secyear.customer_last_name 128 ,t_s_secyear.customer_email_address 129 limit 100