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