github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query5.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 ssr as
    18   (select s_store_id,
    19          sum(sales_price) as sales,
    20          sum(profit) as profit,
    21          sum(return_amt) as `returns`,
    22          sum(net_loss) as profit_loss
    23   from
    24    ( select  ss_store_sk as store_sk,
    25              ss_sold_date_sk  as date_sk,
    26              ss_ext_sales_price as sales_price,
    27              ss_net_profit as profit,
    28              cast(0 as decimal(7,2)) as return_amt,
    29              cast(0 as decimal(7,2)) as net_loss
    30      from store_sales
    31      union all
    32      select sr_store_sk as store_sk,
    33             sr_returned_date_sk as date_sk,
    34             cast(0 as decimal(7,2)) as sales_price,
    35             cast(0 as decimal(7,2)) as profit,
    36             sr_return_amt as return_amt,
    37             sr_net_loss as net_loss
    38      from store_returns
    39     ) salesreturns,
    40       date_dim,
    41       store
    42   where date_sk = d_date_sk
    43         and d_date between cast('1998-08-04' as date)
    44                    and (cast('1998-08-04' as date) +  interval '14' day)
    45         and store_sk = s_store_sk
    46   group by s_store_id)
    47   ,
    48   csr as
    49   (select cp_catalog_page_id,
    50          sum(sales_price) as sales,
    51          sum(profit) as profit,
    52          sum(return_amt) as `returns`,
    53          sum(net_loss) as profit_loss
    54   from
    55    ( select  cs_catalog_page_sk as page_sk,
    56              cs_sold_date_sk  as date_sk,
    57              cs_ext_sales_price as sales_price,
    58              cs_net_profit as profit,
    59              cast(0 as decimal(7,2)) as return_amt,
    60              cast(0 as decimal(7,2)) as net_loss
    61      from catalog_sales
    62      union all
    63      select cr_catalog_page_sk as page_sk,
    64             cr_returned_date_sk as date_sk,
    65             cast(0 as decimal(7,2)) as sales_price,
    66             cast(0 as decimal(7,2)) as profit,
    67             cr_return_amount as return_amt,
    68             cr_net_loss as net_loss
    69      from catalog_returns
    70     ) salesreturns,
    71       date_dim,
    72       catalog_page
    73   where date_sk = d_date_sk
    74         and d_date between cast('1998-08-04' as date)
    75                    and (cast('1998-08-04' as date) +  interval '14' day)
    76         and page_sk = cp_catalog_page_sk
    77   group by cp_catalog_page_id)
    78   ,
    79   wsr as
    80   (select web_site_id,
    81          sum(sales_price) as sales,
    82          sum(profit) as profit,
    83          sum(return_amt) as `returns`,
    84          sum(net_loss) as profit_loss
    85   from
    86    ( select  ws_web_site_sk as wsr_web_site_sk,
    87              ws_sold_date_sk  as date_sk,
    88              ws_ext_sales_price as sales_price,
    89              ws_net_profit as profit,
    90              cast(0 as decimal(7,2)) as return_amt,
    91              cast(0 as decimal(7,2)) as net_loss
    92      from web_sales
    93      union all
    94      select ws_web_site_sk as wsr_web_site_sk,
    95             wr_returned_date_sk as date_sk,
    96             cast(0 as decimal(7,2)) as sales_price,
    97             cast(0 as decimal(7,2)) as profit,
    98             wr_return_amt as return_amt,
    99             wr_net_loss as net_loss
   100      from web_returns left outer join web_sales on
   101           ( wr_item_sk = ws_item_sk
   102             and wr_order_number = ws_order_number)
   103     ) salesreturns,
   104       date_dim,
   105       web_site
   106   where date_sk = d_date_sk
   107         and d_date between cast('1998-08-04' as date)
   108                    and (cast('1998-08-04' as date) +  interval '14' day)
   109         and wsr_web_site_sk = web_site_sk
   110   group by web_site_id)
   111    select  channel
   112          , id
   113          , sum(sales) as sales
   114          , sum(`returns`) as `returns`
   115          , sum(profit) as profit
   116   from
   117   (select 'store channel' as channel
   118          , 'store' || s_store_id as id
   119          , sales
   120          , `returns`
   121          , (profit - profit_loss) as profit
   122   from   ssr
   123   union all
   124   select 'catalog channel' as channel
   125          , 'catalog_page' || cp_catalog_page_id as id
   126          , sales
   127          , `returns`
   128          , (profit - profit_loss) as profit
   129   from  csr
   130   union all
   131   select 'web channel' as channel
   132          , 'web_site' || web_site_id as id
   133          , sales
   134          , `returns`
   135          , (profit - profit_loss) as profit
   136   from   wsr
   137   ) x
   138   group by rollup (channel, id)
   139   order by channel
   140           ,id
   141   limit 100