github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query2.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 wscs as
    18   (select sold_date_sk
    19          ,sales_price
    20    from (select ws_sold_date_sk sold_date_sk
    21                ,ws_ext_sales_price sales_price
    22          from web_sales 
    23          union all
    24          select cs_sold_date_sk sold_date_sk
    25                ,cs_ext_sales_price sales_price
    26          from catalog_sales)),
    27   wswscs as 
    28   (select d_week_seq,
    29          sum(case when (d_day_name='Sunday') then sales_price else null end) sun_sales,
    30          sum(case when (d_day_name='Monday') then sales_price else null end) mon_sales,
    31          sum(case when (d_day_name='Tuesday') then sales_price else  null end) tue_sales,
    32          sum(case when (d_day_name='Wednesday') then sales_price else null end) wed_sales,
    33          sum(case when (d_day_name='Thursday') then sales_price else null end) thu_sales,
    34          sum(case when (d_day_name='Friday') then sales_price else null end) fri_sales,
    35          sum(case when (d_day_name='Saturday') then sales_price else null end) sat_sales
    36   from wscs
    37       ,date_dim
    38   where d_date_sk = sold_date_sk
    39   group by d_week_seq)
    40   select d_week_seq1
    41         ,round(sun_sales1/sun_sales2,2)
    42         ,round(mon_sales1/mon_sales2,2)
    43         ,round(tue_sales1/tue_sales2,2)
    44         ,round(wed_sales1/wed_sales2,2)
    45         ,round(thu_sales1/thu_sales2,2)
    46         ,round(fri_sales1/fri_sales2,2)
    47         ,round(sat_sales1/sat_sales2,2)
    48   from
    49   (select wswscs.d_week_seq d_week_seq1
    50          ,sun_sales sun_sales1
    51          ,mon_sales mon_sales1
    52          ,tue_sales tue_sales1
    53          ,wed_sales wed_sales1
    54          ,thu_sales thu_sales1
    55          ,fri_sales fri_sales1
    56          ,sat_sales sat_sales1
    57    from wswscs,date_dim 
    58    where date_dim.d_week_seq = wswscs.d_week_seq and
    59          d_year = 2001) y,
    60   (select wswscs.d_week_seq d_week_seq2
    61          ,sun_sales sun_sales2
    62          ,mon_sales mon_sales2
    63          ,tue_sales tue_sales2
    64          ,wed_sales wed_sales2
    65          ,thu_sales thu_sales2
    66          ,fri_sales fri_sales2
    67          ,sat_sales sat_sales2
    68    from wswscs
    69        ,date_dim 
    70    where date_dim.d_week_seq = wswscs.d_week_seq and
    71          d_year = 2001+1) z
    72   where d_week_seq1=d_week_seq2-53
    73   order by d_week_seq1