github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query57.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          cc_name,
    20          d_year, d_moy,
    21          sum(cs_sales_price) sum_sales,
    22          avg(sum(cs_sales_price)) over
    23            (partition by i_category, i_brand,
    24                       cc_name, d_year)
    25            avg_monthly_sales,
    26          rank() over
    27            (partition by i_category, i_brand,
    28                       cc_name
    29             order by d_year, d_moy) rn
    30   from item, catalog_sales, date_dim, call_center
    31   where cs_item_sk = i_item_sk and
    32         cs_sold_date_sk = d_date_sk and
    33         cc_call_center_sk= cs_call_center_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            cc_name , d_year, d_moy),
    41   v2 as(
    42   select v1.cc_name
    43          ,v1.d_year, v1.d_moy
    44          ,v1.avg_monthly_sales
    45          ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
    46   from v1, v1 v1_lag, v1 v1_lead
    47   where v1.i_category = v1_lag.i_category and
    48         v1.i_category = v1_lead.i_category and
    49         v1.i_brand = v1_lag.i_brand and
    50         v1.i_brand = v1_lead.i_brand and
    51         v1. cc_name = v1_lag. cc_name and
    52         v1. cc_name = v1_lead. cc_name and
    53         v1.rn = v1_lag.rn + 1 and
    54         v1.rn = v1_lead.rn - 1)
    55    select  *
    56   from v2
    57   where  d_year = 2000 and
    58          avg_monthly_sales > 0 and
    59          case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
    60   order by sum_sales - avg_monthly_sales, nsum
    61   limit 100