github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query58.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_items as
    18   (select i_item_id item_id
    19          ,sum(ss_ext_sales_price) ss_item_rev
    20   from store_sales
    21       ,item
    22       ,date_dim
    23   where ss_item_sk = i_item_sk
    24     and d_date in (select d_date
    25                    from date_dim
    26                    where d_week_seq = (select d_week_seq
    27                                        from date_dim
    28                                        where d_date = '1998-02-19'))
    29     and ss_sold_date_sk   = d_date_sk
    30   group by i_item_id),
    31   cs_items as
    32   (select i_item_id item_id
    33          ,sum(cs_ext_sales_price) cs_item_rev
    34    from catalog_sales
    35        ,item
    36        ,date_dim
    37   where cs_item_sk = i_item_sk
    38    and  d_date in (select d_date
    39                    from date_dim
    40                    where d_week_seq = (select d_week_seq
    41                                        from date_dim
    42                                        where d_date = '1998-02-19'))
    43    and  cs_sold_date_sk = d_date_sk
    44   group by i_item_id),
    45   ws_items as
    46   (select i_item_id item_id
    47          ,sum(ws_ext_sales_price) ws_item_rev
    48    from web_sales
    49        ,item
    50        ,date_dim
    51   where ws_item_sk = i_item_sk
    52    and  d_date in (select d_date
    53                    from date_dim
    54                    where d_week_seq =(select d_week_seq
    55                                       from date_dim
    56                                       where d_date = '1998-02-19'))
    57    and ws_sold_date_sk   = d_date_sk
    58   group by i_item_id)
    59    select  ss_items.item_id
    60         ,ss_item_rev
    61         ,ss_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 ss_dev
    62         ,cs_item_rev
    63         ,cs_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 cs_dev
    64         ,ws_item_rev
    65         ,ws_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 ws_dev
    66         ,(ss_item_rev+cs_item_rev+ws_item_rev)/3 average
    67   from ss_items,cs_items,ws_items
    68   where ss_items.item_id=cs_items.item_id
    69     and ss_items.item_id=ws_items.item_id
    70     and ss_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev
    71     and ss_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev
    72     and cs_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev
    73     and cs_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev
    74     and ws_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev
    75     and ws_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev
    76   order by item_id
    77           ,ss_item_rev
    78   limit 100