github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query89.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(
    19  select i_category, i_class, i_brand,
    20         s_store_name, s_company_name,
    21         d_moy,
    22         sum(ss_sales_price) sum_sales,
    23         avg(sum(ss_sales_price)) over
    24           (partition by i_category, i_brand, s_store_name, s_company_name)
    25           avg_monthly_sales
    26  from item, store_sales, date_dim, store
    27  where ss_item_sk = i_item_sk and
    28        ss_sold_date_sk = d_date_sk and
    29        ss_store_sk = s_store_sk and
    30        d_year in (2000) and
    31          ((i_category in ('Home','Books','Electronics') and
    32            i_class in ('wallpaper','parenting','musical')
    33           )
    34        or (i_category in ('Shoes','Jewelry','Men') and
    35            i_class in ('womens','birdal','pants') 
    36          ))
    37  group by i_category, i_class, i_brand,
    38           s_store_name, s_company_name, d_moy) tmp1
    39  where case when (avg_monthly_sales <> 0) then (abs(sum_sales - avg_monthly_sales) / avg_monthly_sales) else null end > 0.1
    40  order by sum_sales - avg_monthly_sales, s_store_name
    41  limit 100