tpcds.query36.sql Maven / Gradle / Ivy

Show more of this group Show more artifacts with this name
Show all versions of druid Show documentation

An JDBC datasource implementation.

There is a newer version: 1.2.23

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