tpcds.query47.sql Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of druid Show documentation
Show all versions of druid Show documentation
An JDBC datasource implementation.
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 = 1999 or
(d_year = 1999 - 1 and d_moy = 12) or
(d_year = 1999 + 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.s_store_name
, v1.s_company_name
, 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 = 1999
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, 3 limit 100