github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query78.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 ws as
    18    (select d_year AS ws_sold_year, ws_item_sk,
    19      ws_bill_customer_sk ws_customer_sk,
    20      sum(ws_quantity) ws_qty,
    21      sum(ws_wholesale_cost) ws_wc,
    22      sum(ws_sales_price) ws_sp
    23     from web_sales
    24     left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk
    25     join date_dim on ws_sold_date_sk = d_date_sk
    26     where wr_order_number is null
    27     group by d_year, ws_item_sk, ws_bill_customer_sk
    28     ),
    29  cs as
    30    (select d_year AS cs_sold_year, cs_item_sk,
    31      cs_bill_customer_sk cs_customer_sk,
    32      sum(cs_quantity) cs_qty,
    33      sum(cs_wholesale_cost) cs_wc,
    34      sum(cs_sales_price) cs_sp
    35     from catalog_sales
    36     left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk
    37     join date_dim on cs_sold_date_sk = d_date_sk
    38     where cr_order_number is null
    39     group by d_year, cs_item_sk, cs_bill_customer_sk
    40     ),
    41  ss as
    42    (select d_year AS ss_sold_year, ss_item_sk,
    43      ss_customer_sk,
    44      sum(ss_quantity) ss_qty,
    45      sum(ss_wholesale_cost) ss_wc,
    46      sum(ss_sales_price) ss_sp
    47     from store_sales
    48     left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk
    49     join date_dim on ss_sold_date_sk = d_date_sk
    50     where sr_ticket_number is null
    51     group by d_year, ss_item_sk, ss_customer_sk
    52     )
    53   select 
    54  ss_sold_year, ss_item_sk, ss_customer_sk,
    55  round(ss_qty/(coalesce(ws_qty,0)+coalesce(cs_qty,0)),2) ratio,
    56  ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
    57  coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
    58  coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
    59  coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
    60  from ss
    61  left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk)
    62  left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=ss_item_sk and cs_customer_sk=ss_customer_sk)
    63  where (coalesce(ws_qty,0)>0 or coalesce(cs_qty, 0)>0) and ss_sold_year=2000
    64  order by 
    65    ss_sold_year, ss_item_sk, ss_customer_sk,
    66    ss_qty desc, ss_wc desc, ss_sp desc,
    67    other_chan_qty,
    68    other_chan_wholesale_cost,
    69    other_chan_sales_price,
    70    ratio
    71  limit 100