--!syntax_pg
--TPC-DS Q86

-- start query 1 in stream 0 using template ../query_templates/query86.tpl
select   
    sum(ws_net_paid) as total_sum
   ,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(ws_net_paid) desc) as rank_within_parent
 from
    plato.web_sales
   ,plato.date_dim       d1
   ,plato.item
 where
    d1.d_month_seq between 1212 and 1212+11
 and d1.d_date_sk = ws_sold_date_sk
 and i_item_sk  = ws_item_sk
 group by rollup(i_category,i_class)
 order by
   lochierarchy desc,
   case when lochierarchy = 0 then i_category end,
   rank_within_parent
 limit 100;

-- end query 1 in stream 0 using template ../query_templates/query86.tpl