github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query23b.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 frequent_ss_items as
    18   (select substring(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt
    19    from store_sales
    20        ,date_dim
    21        ,item
    22    where ss_sold_date_sk = d_date_sk
    23      and ss_item_sk = i_item_sk
    24      and d_year in (1999,1999 + 1,1999 + 2,1999 + 3)
    25    group by substring(i_item_desc,1,30),i_item_sk,d_date
    26    having count(*) >4),
    27   max_store_sales as
    28   (select max(csales) tpcds_cmax
    29    from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales
    30          from store_sales
    31              ,customer
    32              ,date_dim
    33          where ss_customer_sk = c_customer_sk
    34           and ss_sold_date_sk = d_date_sk
    35           and d_year in (1999,1999+1,1999+2,1999+3)
    36          group by c_customer_sk) x),
    37   best_ss_customer as
    38   (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales
    39    from store_sales
    40        ,customer
    41    where ss_customer_sk = c_customer_sk
    42    group by c_customer_sk
    43    having sum(ss_quantity*ss_sales_price) > (95/100.0) * (select
    44    *
    45   from max_store_sales))
    46    select  c_last_name,c_first_name,sales
    47   from ((select c_last_name,c_first_name,sum(cs_quantity*cs_list_price) sales
    48          from catalog_sales
    49              ,customer
    50              ,date_dim
    51          where d_year = 1999
    52           and d_moy = 1
    53           and cs_sold_date_sk = d_date_sk
    54           and cs_item_sk in (select item_sk from frequent_ss_items)
    55           and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer)
    56           and cs_bill_customer_sk = c_customer_sk
    57         group by c_last_name,c_first_name)
    58        union all
    59        (select c_last_name,c_first_name,sum(ws_quantity*ws_list_price) sales
    60         from web_sales
    61             ,customer
    62             ,date_dim
    63         where d_year = 1999
    64           and d_moy = 1
    65           and ws_sold_date_sk = d_date_sk
    66           and ws_item_sk in (select item_sk from frequent_ss_items)
    67           and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer)
    68           and ws_bill_customer_sk = c_customer_sk
    69         group by c_last_name,c_first_name)) y
    70       order by c_last_name,c_first_name,sales
    71    limit 100