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
A 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