====
---- QUERY: TPCDS-Q36
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')
 group by rollup(i_category,i_class)
 order by
   lochierarchy desc
  -- Modified from original because Impala does not allow aliases within order by
  -- expressions, except at the top level.
  ,case when grouping(i_category)+grouping(i_class) = 0 then i_category end
  ,rank_within_parent
  limit 100
---- RESULTS
-0.434627,'NULL','NULL',2,1
-0.445726,'Electronics','NULL',1,1
-0.439058,'Children','NULL',1,2
-0.435375,'Home','NULL',1,3
-0.435135,'Sports','NULL',1,4
-0.434787,'Shoes','NULL',1,5
-0.433937,'Men','NULL',1,6
-0.432814,'Books','NULL',1,7
-0.432786,'Women','NULL',1,8
-0.428839,'Jewelry','NULL',1,9
-0.428397,'Music','NULL',1,10
-0.408364,'NULL','NULL',1,11
-0.493138,'Books','NULL',0,1
-0.459060,'Books','self-help',0,2
-0.453614,'Books','romance',0,3
-0.453097,'Books','parenting',0,4
-0.452429,'Books','home repair',0,5
-0.439811,'Books','arts',0,6
-0.437717,'Books','computers',0,7
-0.436171,'Books','entertainments',0,8
-0.433511,'Books','business',0,9
-0.429332,'Books','cooking',0,10
-0.427767,'Books','history',0,11
-0.426951,'Books','sports',0,12
-0.425222,'Books','fiction',0,13
-0.421390,'Books','travel',0,14
-0.413109,'Books','reference',0,15
-0.407932,'Books','science',0,16
-0.402453,'Books','mystery',0,17
-0.519729,'Children','NULL',0,1
-0.443955,'Children','infants',0,2
-0.441166,'Children','toddlers',0,3
-0.439389,'Children','newborn',0,4
-0.431534,'Children','school-uniforms',0,5
-0.481111,'Electronics','scanners',0,1
-0.474762,'Electronics','wireless',0,2
-0.468814,'Electronics','musical',0,3
-0.459644,'Electronics','audio',0,4
-0.459285,'Electronics','portable',0,5
-0.457669,'Electronics','automotive',0,6
-0.450597,'Electronics','personal',0,7
-0.446587,'Electronics','karoke',0,8
-0.444758,'Electronics','camcorders',0,9
-0.442434,'Electronics','stereo',0,10
-0.435633,'Electronics','monitors',0,11
-0.430062,'Electronics','disk drives',0,12
-0.429565,'Electronics','dvd/vcr players',0,13
-0.427792,'Electronics','televisions',0,14
-0.427485,'Electronics','cameras',0,15
-0.417896,'Electronics','memory',0,16
-0.458207,'Home','wallpaper',0,1
-0.458124,'Home','rugs',0,2
-0.453909,'Home','blinds/shades',0,3
-0.450805,'Home','tables',0,4
-0.450006,'Home','bedding',0,5
-0.444429,'Home','paint',0,6
-0.442900,'Home','kids',0,7
-0.441957,'Home','decor',0,8
-0.440270,'Home','curtains/drapes',0,9
-0.436733,'Home','accent',0,10
-0.429606,'Home','glassware',0,11
-0.421293,'Home','flatware',0,12
-0.419606,'Home','mattresses',0,13
-0.416797,'Home','furniture',0,14
-0.416138,'Home','bathroom',0,15
-0.397446,'Home','lighting',0,16
-0.275087,'Home','NULL',0,17
-0.482023,'Jewelry','consignment',0,1
-0.452923,'Jewelry','gold',0,2
-0.442266,'Jewelry','womens watch',0,3
-0.438958,'Jewelry','semi-precious',0,4
-0.437528,'Jewelry','NULL',0,5
-0.435691,'Jewelry','bracelets',0,6
-0.434631,'Jewelry','birdal',0,7
-0.434527,'Jewelry','loose stones',0,8
-0.433407,'Jewelry','rings',0,9
-0.430755,'Jewelry','estate',0,10
-0.429736,'Jewelry','pendants',0,11
-0.422080,'Jewelry','earings',0,12
-0.417512,'Jewelry','costume',0,13
-0.412604,'Jewelry','jewelry boxes',0,14
-0.412515,'Jewelry','mens watch',0,15
-0.405426,'Jewelry','custom',0,16
-0.403669,'Jewelry','diamonds',0,17
-0.440736,'Men','sports-apparel',0,1
-0.437901,'Men','accessories',0,2
-0.435914,'Men','pants',0,3
-0.423606,'Men','shirts',0,4
-0.376248,'Men','NULL',0,5
-0.474311,'Music','NULL',0,1
-0.433297,'Music','pop',0,2
-0.430202,'Music','country',0,3
-0.427121,'Music','classical',0,4
-0.421368,'Music','rock',0,5
-0.644087,'Shoes','NULL',0,1
-0.443544,'Shoes','mens',0,2
-0.442976,'Shoes','womens',0,3
-0.430849,'Shoes','kids',0,4
-0.420041,'Shoes','athletic',0,5
-0.467490,'Sports','basketball',0,1
---- TYPES
DECIMAL, STRING, STRING, SMALLINT, BIGINT
====
