github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query28.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  select  *
    18  from (select avg(ss_list_price) B1_LP
    19              ,count(ss_list_price) B1_CNT
    20              ,count(distinct ss_list_price) B1_CNTD
    21        from store_sales
    22        where ss_quantity between 0 and 5
    23          and (ss_list_price between 11 and 11+10 
    24               or ss_coupon_amt between 460 and 460+1000
    25               or ss_wholesale_cost between 14 and 14+20)) B1,
    26       (select avg(ss_list_price) B2_LP
    27              ,count(ss_list_price) B2_CNT
    28              ,count(distinct ss_list_price) B2_CNTD
    29        from store_sales
    30        where ss_quantity between 6 and 10
    31          and (ss_list_price between 91 and 91+10
    32            or ss_coupon_amt between 1430 and 1430+1000
    33            or ss_wholesale_cost between 32 and 32+20)) B2,
    34       (select avg(ss_list_price) B3_LP
    35              ,count(ss_list_price) B3_CNT
    36              ,count(distinct ss_list_price) B3_CNTD
    37        from store_sales
    38        where ss_quantity between 11 and 15
    39          and (ss_list_price between 66 and 66+10
    40            or ss_coupon_amt between 920 and 920+1000
    41            or ss_wholesale_cost between 4 and 4+20)) B3,
    42       (select avg(ss_list_price) B4_LP
    43              ,count(ss_list_price) B4_CNT
    44              ,count(distinct ss_list_price) B4_CNTD
    45        from store_sales
    46        where ss_quantity between 16 and 20
    47          and (ss_list_price between 142 and 142+10
    48            or ss_coupon_amt between 3054 and 3054+1000
    49            or ss_wholesale_cost between 80 and 80+20)) B4,
    50       (select avg(ss_list_price) B5_LP
    51              ,count(ss_list_price) B5_CNT
    52              ,count(distinct ss_list_price) B5_CNTD
    53        from store_sales
    54        where ss_quantity between 21 and 25
    55          and (ss_list_price between 135 and 135+10
    56            or ss_coupon_amt between 14180 and 14180+1000
    57            or ss_wholesale_cost between 38 and 38+20)) B5,
    58       (select avg(ss_list_price) B6_LP
    59              ,count(ss_list_price) B6_CNT
    60              ,count(distinct ss_list_price) B6_CNTD
    61        from store_sales
    62        where ss_quantity between 26 and 30
    63          and (ss_list_price between 28 and 28+10
    64            or ss_coupon_amt between 2513 and 2513+1000
    65            or ss_wholesale_cost between 42 and 42+20)) B6
    66  limit 100