github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query83.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 sr_items as
    18   (select i_item_id item_id,
    19          sum(sr_return_quantity) sr_item_qty
    20   from store_returns,
    21        item,
    22        date_dim
    23   where sr_item_sk = i_item_sk
    24   and   d_date    in 
    25  	(select d_date
    26  	from date_dim
    27  	where d_week_seq in 
    28  		(select d_week_seq
    29  		from date_dim
    30  	  where d_date in ('1998-01-02','1998-10-15','1998-11-10')))
    31   and   sr_returned_date_sk   = d_date_sk
    32   group by i_item_id),
    33   cr_items as
    34   (select i_item_id item_id,
    35          sum(cr_return_quantity) cr_item_qty
    36   from catalog_returns,
    37        item,
    38        date_dim
    39   where cr_item_sk = i_item_sk
    40   and   d_date    in 
    41  	(select d_date
    42  	from date_dim
    43  	where d_week_seq in 
    44  		(select d_week_seq
    45  		from date_dim
    46  	  where d_date in ('1998-01-02','1998-10-15','1998-11-10')))
    47   and   cr_returned_date_sk   = d_date_sk
    48   group by i_item_id),
    49   wr_items as
    50   (select i_item_id item_id,
    51          sum(wr_return_quantity) wr_item_qty
    52   from web_returns,
    53        item,
    54        date_dim
    55   where wr_item_sk = i_item_sk
    56   and   d_date    in 
    57  	(select d_date
    58  	from date_dim
    59  	where d_week_seq in 
    60  		(select d_week_seq
    61  		from date_dim
    62  		where d_date in ('1998-01-02','1998-10-15','1998-11-10')))
    63   and   wr_returned_date_sk   = d_date_sk
    64   group by i_item_id)
    65    select  sr_items.item_id
    66         ,sr_item_qty
    67         ,sr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 sr_dev
    68         ,cr_item_qty
    69         ,cr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 cr_dev
    70         ,wr_item_qty
    71         ,wr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 wr_dev
    72         ,(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 average
    73   from sr_items
    74       ,cr_items
    75       ,wr_items
    76   where sr_items.item_id=cr_items.item_id
    77     and sr_items.item_id=wr_items.item_id 
    78   order by sr_items.item_id
    79           ,sr_item_qty
    80   limit 100