| -- Licensed to the Apache Software Foundation (ASF) under one |
| -- or more contributor license agreements. See the NOTICE file |
| -- distributed with this work for additional information |
| -- regarding copyright ownership. The ASF licenses this file |
| -- to you under the Apache License, Version 2.0 (the |
| -- "License"); you may not use this file except in compliance |
| -- with the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, software |
| -- distributed under the License is distributed on an "AS IS" BASIS, |
| -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| -- See the License for the specific language governing permissions and |
| -- limitations under the License. |
| |
| with year_total as ( |
| select c_customer_id customer_id |
| ,c_first_name customer_first_name |
| ,c_last_name customer_last_name |
| ,d_year as year |
| ,max(ss_net_paid) year_total |
| ,'s' sale_type |
| from customer |
| ,store_sales |
| ,date_dim |
| where c_customer_sk = ss_customer_sk |
| and ss_sold_date_sk = d_date_sk |
| and d_year in (2001,2001+1) |
| group by c_customer_id |
| ,c_first_name |
| ,c_last_name |
| ,d_year |
| union all |
| select c_customer_id customer_id |
| ,c_first_name customer_first_name |
| ,c_last_name customer_last_name |
| ,d_year as year |
| ,max(ws_net_paid) year_total |
| ,'w' sale_type |
| from customer |
| ,web_sales |
| ,date_dim |
| where c_customer_sk = ws_bill_customer_sk |
| and ws_sold_date_sk = d_date_sk |
| and d_year in (2001,2001+1) |
| group by c_customer_id |
| ,c_first_name |
| ,c_last_name |
| ,d_year |
| ) |
| select |
| t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name |
| from year_total t_s_firstyear |
| ,year_total t_s_secyear |
| ,year_total t_w_firstyear |
| ,year_total t_w_secyear |
| where t_s_secyear.customer_id = t_s_firstyear.customer_id |
| and t_s_firstyear.customer_id = t_w_secyear.customer_id |
| and t_s_firstyear.customer_id = t_w_firstyear.customer_id |
| and t_s_firstyear.sale_type = 's' |
| and t_w_firstyear.sale_type = 'w' |
| and t_s_secyear.sale_type = 's' |
| and t_w_secyear.sale_type = 'w' |
| and t_s_firstyear.year = 2001 |
| and t_s_secyear.year = 2001+1 |
| and t_w_firstyear.year = 2001 |
| and t_w_secyear.year = 2001+1 |
| and t_s_firstyear.year_total > 0 |
| and t_w_firstyear.year_total > 0 |
| and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end |
| > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end |
| order by 2,1,3 |
| limit 100 |