tpcds.query36.sql Maven / Gradle / Ivy
select
sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
,i_category
,i_class
,grouping(i_category)+grouping(i_class) as lochierarchy
,rank() over (
partition by grouping(i_category)+grouping(i_class),
case when grouping(i_class) = 0 then i_category end
order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as rank_within_parent
from
store_sales
,date_dim d1
,item
,store
where
d1.d_year = 2001
and d1.d_date_sk = ss_sold_date_sk
and i_item_sk = ss_item_sk
and s_store_sk = ss_store_sk
and s_state in ('TN','TN','TN','TN',
'TN','TN','TN','TN')
group by rollup(i_category,i_class)
order by
lochierarchy desc
,case when lochierarchy = 0 then i_category end
,rank_within_parent
limit 100