tpcds.query57.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,
cc_name,
d_year,
d_moy,
sum(cs_sales_price) sum_sales,
avg(sum(cs_sales_price)) over (partition by i_category, i_brand,
cc_name, d_year)
avg_monthly_sales, rank() over (partition by i_category, i_brand,
cc_name
order by d_year, d_moy) rn
from item,
catalog_sales,
date_dim,
call_center
where cs_item_sk = i_item_sk
and cs_sold_date_sk = d_date_sk
and cc_call_center_sk = cs_call_center_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,
cc_name, d_year, d_moy),
v2 as (select v1.i_category
, v1.i_brand
, v1.cc_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.cc_name = v1_lag.cc_name
and v1.cc_name = v1_lead.cc_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