github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query31.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 ss as
    18   (select ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales
    19   from store_sales,date_dim,customer_address
    20   where ss_sold_date_sk = d_date_sk
    21    and ss_addr_sk=ca_address_sk
    22   group by ca_county,d_qoy, d_year),
    23   ws as
    24   (select ca_county,d_qoy, d_year,sum(ws_ext_sales_price) as web_sales
    25   from web_sales,date_dim,customer_address
    26   where ws_sold_date_sk = d_date_sk
    27    and ws_bill_addr_sk=ca_address_sk
    28   group by ca_county,d_qoy, d_year)
    29   select 
    30          ss1.ca_county
    31         ,ss1.d_year
    32         ,ws2.web_sales/ws1.web_sales web_q1_q2_increase
    33         ,ss2.store_sales/ss1.store_sales store_q1_q2_increase
    34         ,ws3.web_sales/ws2.web_sales web_q2_q3_increase
    35         ,ss3.store_sales/ss2.store_sales store_q2_q3_increase
    36   from
    37          ss ss1
    38         ,ss ss2
    39         ,ss ss3
    40         ,ws ws1
    41         ,ws ws2
    42         ,ws ws3
    43   where
    44      ss1.d_qoy = 1
    45      and ss1.d_year = 2000
    46      and ss1.ca_county = ss2.ca_county
    47      and ss2.d_qoy = 2
    48      and ss2.d_year = 2000
    49   and ss2.ca_county = ss3.ca_county
    50      and ss3.d_qoy = 3
    51      and ss3.d_year = 2000
    52      and ss1.ca_county = ws1.ca_county
    53      and ws1.d_qoy = 1
    54      and ws1.d_year = 2000
    55      and ws1.ca_county = ws2.ca_county
    56      and ws2.d_qoy = 2
    57      and ws2.d_year = 2000
    58      and ws1.ca_county = ws3.ca_county
    59      and ws3.d_qoy = 3
    60      and ws3.d_year =2000
    61      and case when ws1.web_sales > 0 then ws2.web_sales/ws1.web_sales else null end 
    62         > case when ss1.store_sales > 0 then ss2.store_sales/ss1.store_sales else null end
    63      and case when ws2.web_sales > 0 then ws3.web_sales/ws2.web_sales else null end
    64         > case when ss2.store_sales > 0 then ss3.store_sales/ss2.store_sales else null end
    65   order by ss1.d_year