| -- Licensed to the Apache Software Foundation (ASF) under one |
| -- or more contributor license agreements. See the NOTICE file |
| -- distributed with this work for additional information |
| -- regarding copyright ownership. The ASF licenses this file |
| -- to you under the Apache License, Version 2.0 (the |
| -- "License"); you may not use this file except in compliance |
| -- with the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, software |
| -- distributed under the License is distributed on an "AS IS" BASIS, |
| -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| -- See the License for the specific language governing permissions and |
| -- limitations under the License. |
| |
| with v1 as( |
| select i_category, i_brand, |
| s_store_name, s_company_name, |
| d_year, d_moy, |
| sum(ss_sales_price) sum_sales, |
| avg(sum(ss_sales_price)) over |
| (partition by i_category, i_brand, |
| s_store_name, s_company_name, d_year) |
| avg_monthly_sales, |
| rank() over |
| (partition by i_category, i_brand, |
| s_store_name, s_company_name |
| order by d_year, d_moy) rn |
| from item, store_sales, date_dim, store |
| where ss_item_sk = i_item_sk and |
| ss_sold_date_sk = d_date_sk and |
| ss_store_sk = s_store_sk and |
| ( |
| d_year = 2000 or |
| ( d_year = 2000-1 and d_moy =12) or |
| ( d_year = 2000+1 and d_moy =1) |
| ) |
| group by i_category, i_brand, |
| s_store_name, s_company_name, |
| d_year, d_moy), |
| v2 as( |
| select v1.i_category, v1.i_brand |
| ,v1.d_year, v1.d_moy |
| ,v1.avg_monthly_sales |
| ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum |
| from v1, v1 v1_lag, v1 v1_lead |
| where v1.i_category = v1_lag.i_category and |
| v1.i_category = v1_lead.i_category and |
| v1.i_brand = v1_lag.i_brand and |
| v1.i_brand = v1_lead.i_brand and |
| v1.s_store_name = v1_lag.s_store_name and |
| v1.s_store_name = v1_lead.s_store_name and |
| v1.s_company_name = v1_lag.s_company_name and |
| v1.s_company_name = v1_lead.s_company_name and |
| v1.rn = v1_lag.rn + 1 and |
| v1.rn = v1_lead.rn - 1) |
| select * |
| from v2 |
| where d_year = 2000 and |
| avg_monthly_sales > 0 and |
| case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1 |
| order by sum_sales - avg_monthly_sales, nsum |
| limit 100 |