github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query23a.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
    46   max_store_sales))
    47    select  sum(sales)
    48   from ((select cs_quantity*cs_list_price sales
    49         from catalog_sales
    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        union all
    57        (select ws_quantity*ws_list_price sales
    58         from web_sales
    59             ,date_dim
    60         where d_year = 1999
    61           and d_moy = 1
    62           and ws_sold_date_sk = d_date_sk
    63           and ws_item_sk in (select item_sk from frequent_ss_items)
    64           and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer))) y
    65   limit 100