text: ====
  ---- 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
  ====
  