github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query56.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 i_item_id,sum(ss_ext_sales_price) total_sales
    19   from
    20   	store_sales,
    21   	date_dim,
    22           customer_address,
    23           item
    24   where i_item_id in (select
    25       i_item_id
    26  from item
    27  where i_color in ('orchid','chiffon','lace'))
    28   and     ss_item_sk              = i_item_sk
    29   and     ss_sold_date_sk         = d_date_sk
    30   and     d_year                  = 2000
    31   and     d_moy                   = 1
    32   and     ss_addr_sk              = ca_address_sk
    33   and     ca_gmt_offset           = -8 
    34   group by i_item_id),
    35   cs as (
    36   select i_item_id,sum(cs_ext_sales_price) total_sales
    37   from
    38   	catalog_sales,
    39   	date_dim,
    40           customer_address,
    41           item
    42   where
    43           i_item_id               in (select
    44    i_item_id
    45  from item
    46  where i_color in ('orchid','chiffon','lace'))
    47   and     cs_item_sk              = i_item_sk
    48   and     cs_sold_date_sk         = d_date_sk
    49   and     d_year                  = 2000
    50   and     d_moy                   = 1
    51   and     cs_bill_addr_sk         = ca_address_sk
    52   and     ca_gmt_offset           = -8 
    53   group by i_item_id),
    54   ws as (
    55   select i_item_id,sum(ws_ext_sales_price) total_sales
    56   from
    57   	web_sales,
    58   	date_dim,
    59           customer_address,
    60           item
    61   where
    62           i_item_id               in (select
    63    i_item_id
    64  from item
    65  where i_color in ('orchid','chiffon','lace'))
    66   and     ws_item_sk              = i_item_sk
    67   and     ws_sold_date_sk         = d_date_sk
    68   and     d_year                  = 2000
    69   and     d_moy                   = 1
    70   and     ws_bill_addr_sk         = ca_address_sk
    71   and     ca_gmt_offset           = -8
    72   group by i_item_id)
    73    select  i_item_id ,sum(total_sales) total_sales
    74   from  (select * from ss 
    75          union all
    76          select * from cs 
    77          union all
    78          select * from ws) tmp1
    79   group by i_item_id
    80   order by total_sales,
    81            i_item_id
    82   limit 100