github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query74.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         ,d_year as `year`
    22         ,max(ss_net_paid) year_total
    23         ,'s' sale_type
    24   from customer
    25       ,store_sales
    26       ,date_dim
    27   where c_customer_sk = ss_customer_sk
    28     and ss_sold_date_sk = d_date_sk
    29     and d_year in (2001,2001+1)
    30   group by c_customer_id
    31           ,c_first_name
    32           ,c_last_name
    33           ,d_year
    34   union all
    35   select c_customer_id customer_id
    36         ,c_first_name customer_first_name
    37         ,c_last_name customer_last_name
    38         ,d_year as `year`
    39         ,max(ws_net_paid) year_total
    40         ,'w' sale_type
    41   from customer
    42       ,web_sales
    43       ,date_dim
    44   where c_customer_sk = ws_bill_customer_sk
    45     and ws_sold_date_sk = d_date_sk
    46     and d_year in (2001,2001+1)
    47   group by c_customer_id
    48           ,c_first_name
    49           ,c_last_name
    50           ,d_year
    51           )
    52    select
    53          t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name
    54   from year_total t_s_firstyear
    55       ,year_total t_s_secyear
    56       ,year_total t_w_firstyear
    57       ,year_total t_w_secyear
    58   where t_s_secyear.customer_id = t_s_firstyear.customer_id
    59           and t_s_firstyear.customer_id = t_w_secyear.customer_id
    60           and t_s_firstyear.customer_id = t_w_firstyear.customer_id
    61           and t_s_firstyear.sale_type = 's'
    62           and t_w_firstyear.sale_type = 'w'
    63           and t_s_secyear.sale_type = 's'
    64           and t_w_secyear.sale_type = 'w'
    65           and t_s_firstyear.`year` = 2001
    66           and t_s_secyear.`year` = 2001+1
    67           and t_w_firstyear.`year` = 2001
    68           and t_w_secyear.`year` = 2001+1
    69           and t_s_firstyear.year_total > 0
    70           and t_w_firstyear.year_total > 0
    71           and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end
    72             > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end
    73   order by 2,1,3
    74  limit 100