github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query47.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 v1 as(
    18   select i_category, i_brand,
    19          s_store_name, s_company_name,
    20          d_year, d_moy,
    21          sum(ss_sales_price) sum_sales,
    22          avg(sum(ss_sales_price)) over
    23            (partition by i_category, i_brand,
    24                       s_store_name, s_company_name, d_year)
    25            avg_monthly_sales,
    26          rank() over
    27            (partition by i_category, i_brand,
    28                       s_store_name, s_company_name
    29             order by d_year, d_moy) rn
    30   from item, store_sales, date_dim, store
    31   where ss_item_sk = i_item_sk and
    32         ss_sold_date_sk = d_date_sk and
    33         ss_store_sk = s_store_sk and
    34         (
    35           d_year = 2000 or
    36           ( d_year = 2000-1 and d_moy =12) or
    37           ( d_year = 2000+1 and d_moy =1)
    38         )
    39   group by i_category, i_brand,
    40            s_store_name, s_company_name,
    41            d_year, d_moy),
    42   v2 as(
    43   select v1.i_category
    44          ,v1.d_year, v1.d_moy
    45          ,v1.avg_monthly_sales
    46          ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
    47   from v1, v1 v1_lag, v1 v1_lead
    48   where v1.i_category = v1_lag.i_category and
    49         v1.i_category = v1_lead.i_category and
    50         v1.i_brand = v1_lag.i_brand and
    51         v1.i_brand = v1_lead.i_brand and
    52         v1.s_store_name = v1_lag.s_store_name and
    53         v1.s_store_name = v1_lead.s_store_name and
    54         v1.s_company_name = v1_lag.s_company_name and
    55         v1.s_company_name = v1_lead.s_company_name and
    56         v1.rn = v1_lag.rn + 1 and
    57         v1.rn = v1_lead.rn - 1)
    58    select  *
    59   from v2
    60   where  d_year = 2000 and
    61          avg_monthly_sales > 0 and
    62          case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
    63   order by sum_sales - avg_monthly_sales, 3
    64   limit 100