github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query77.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 s_store_sk,
    19           sum(ss_ext_sales_price) as sales,
    20           sum(ss_net_profit) as profit
    21   from store_sales,
    22        date_dim,
    23        store
    24   where ss_sold_date_sk = d_date_sk
    25         and d_date between cast('1998-08-04' as date)
    26                    and (cast('1998-08-04' as date) +  interval '30' day)
    27         and ss_store_sk = s_store_sk
    28   group by s_store_sk)
    29   ,
    30   sr as
    31   (select s_store_sk,
    32           sum(sr_return_amt) as `returns`,
    33           sum(sr_net_loss) as profit_loss
    34   from store_returns,
    35        date_dim,
    36        store
    37   where sr_returned_date_sk = d_date_sk
    38         and d_date between cast('1998-08-04' as date)
    39                    and (cast('1998-08-04' as date) +  interval '30' day)
    40         and sr_store_sk = s_store_sk
    41   group by s_store_sk),
    42   cs as
    43   (select cs_call_center_sk,
    44          sum(cs_ext_sales_price) as sales,
    45          sum(cs_net_profit) as profit
    46   from catalog_sales,
    47        date_dim
    48   where cs_sold_date_sk = d_date_sk
    49         and d_date between cast('1998-08-04' as date)
    50                    and (cast('1998-08-04' as date) +  interval '30' day)
    51   group by cs_call_center_sk
    52   ),
    53   cr as
    54   (select
    55          sum(cr_return_amount) as `returns`,
    56          sum(cr_net_loss) as profit_loss
    57   from catalog_returns,
    58        date_dim
    59   where cr_returned_date_sk = d_date_sk
    60         and d_date between cast('1998-08-04' as date)
    61                    and (cast('1998-08-04' as date) +  interval '30' day)
    62   ),
    63   ws as
    64   ( select wp_web_page_sk,
    65          sum(ws_ext_sales_price) as sales,
    66          sum(ws_net_profit) as profit
    67   from web_sales,
    68        date_dim,
    69        web_page
    70   where ws_sold_date_sk = d_date_sk
    71         and d_date between cast('1998-08-04' as date)
    72                    and (cast('1998-08-04' as date) +  interval '30' day)
    73         and ws_web_page_sk = wp_web_page_sk
    74   group by wp_web_page_sk),
    75   wr as
    76   (select wp_web_page_sk,
    77          sum(wr_return_amt) as `returns`,
    78          sum(wr_net_loss) as profit_loss
    79   from web_returns,
    80        date_dim,
    81        web_page
    82   where wr_returned_date_sk = d_date_sk
    83         and d_date between cast('1998-08-04' as date)
    84                    and (cast('1998-08-04' as date) +  interval '30' day)
    85         and wr_web_page_sk = wp_web_page_sk
    86   group by wp_web_page_sk)
    87    select  channel
    88          , id
    89          , sum(sales) as sales
    90          , sum(`returns`) as `returns`
    91          , sum(profit) as profit
    92   from
    93   (select 'store channel' as channel
    94          , ss.s_store_sk as id
    95          , sales
    96          , coalesce(`returns`, 0) as `returns`
    97          , (profit - coalesce(profit_loss,0)) as profit
    98   from   ss left join sr
    99          on  ss.s_store_sk = sr.s_store_sk
   100   union all
   101   select 'catalog channel' as channel
   102          , cs_call_center_sk as id
   103          , sales
   104          , `returns`
   105          , (profit - profit_loss) as profit
   106   from  cs
   107         , cr
   108   union all
   109   select 'web channel' as channel
   110          , ws.wp_web_page_sk as id
   111          , sales
   112          , coalesce(`returns`, 0) `returns`
   113          , (profit - coalesce(profit_loss,0)) as profit
   114   from   ws left join wr
   115          on  ws.wp_web_page_sk = wr.wp_web_page_sk
   116   ) x
   117   group by rollup (channel, id)
   118   order by channel
   119           ,id
   120   limit 100