====
---- QUERY: TPCDS-Q67A
with results as
(     select i_category ,i_class ,i_brand ,i_product_name ,d_year ,d_qoy ,d_moy ,s_store_id
                  ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales
            from store_sales ,date_dim ,store ,item
       where  ss_sold_date_sk=d_date_sk
          and ss_item_sk=i_item_sk
          and ss_store_sk = s_store_sk
          and d_month_seq between 1200 and 1200 + 11
       group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id)
 ,
 results_rollup as
 (select i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, s_store_id, sumsales
  from results
  union all
  select i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, null s_store_id, sum(sumsales) sumsales
  from results
  group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy
  union all
  select i_category, i_class, i_brand, i_product_name, d_year, d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales
  from results
  group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy
  union all
  select i_category, i_class, i_brand, i_product_name, d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales
  from results
  group by i_category, i_class, i_brand, i_product_name, d_year
  union all
  select i_category, i_class, i_brand, i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales
  from results
  group by i_category, i_class, i_brand, i_product_name
  union all
  select i_category, i_class, i_brand, null i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales
  from results
  group by i_category, i_class, i_brand
  union all
  select i_category, i_class, null i_brand, null i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales
  from results
  group by i_category, i_class
  union all
  select i_category, null i_class, null i_brand, null i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales
  from results
  group by i_category
  union all
  select null i_category, null i_class, null i_brand, null i_product_name, null d_year, null d_qoy, null d_moy, null s_store_id, sum(sumsales) sumsales
  from results)
 select  *
from (select i_category
            ,i_class
            ,i_brand
            ,i_product_name
            ,d_year
            ,d_qoy
            ,d_moy
            ,s_store_id
            ,sumsales
            ,rank() over (partition by i_category order by sumsales desc) rk
      from results_rollup) dw2
where rk <= 100
order by i_category
        ,i_class
        ,i_brand
        ,i_product_name
        ,d_year
        ,d_qoy
        ,d_moy
        ,s_store_id
        ,sumsales
        ,rk
limit 100;
---- RESULTS
'Books','arts','amalgmaxi #12','NULL',NULL,NULL,NULL,'NULL',1577322.20,26
'Books','arts','amalgmaxi #2','NULL',NULL,NULL,NULL,'NULL',757558.15,93
'Books','arts','amalgmaxi #3','NULL',NULL,NULL,NULL,'NULL',889848.65,79
'Books','arts','amalgmaxi #9','NULL',NULL,NULL,NULL,'NULL',816219.08,89
'Books','arts','NULL','NULL',NULL,NULL,NULL,'NULL',5188162.15,17
'Books','business','importomaxi #12','NULL',NULL,NULL,NULL,'NULL',1288307.29,42
'Books','business','importomaxi #2','NULL',NULL,NULL,NULL,'NULL',985887.08,68
'Books','business','importomaxi #8','NULL',NULL,NULL,NULL,'NULL',1067725.65,61
'Books','business','importomaxi #9','NULL',NULL,NULL,NULL,'NULL',1269598.91,45
'Books','business','NULL','NULL',NULL,NULL,NULL,'NULL',5905977.34,10
'Books','computers','exportimaxi #12','NULL',NULL,NULL,NULL,'NULL',1456918.73,28
'Books','computers','exportimaxi #2','NULL',NULL,NULL,NULL,'NULL',1626664.04,24
'Books','computers','exportimaxi #3','NULL',NULL,NULL,NULL,'NULL',875667.64,82
'Books','computers','exportimaxi #6','NULL',NULL,NULL,NULL,'NULL',847659.03,86
'Books','computers','exportimaxi #8','NULL',NULL,NULL,NULL,'NULL',1189646.65,49
'Books','computers','exportimaxi #9','NULL',NULL,NULL,NULL,'NULL',1059845.98,63
'Books','computers','NULL','NULL',NULL,NULL,NULL,'NULL',7061536.03,7
'Books','cooking','amalgunivamalg #12','NULL',NULL,NULL,NULL,'NULL',1097730.00,58
'Books','cooking','amalgunivamalg #3','NULL',NULL,NULL,NULL,'NULL',978431.47,69
'Books','cooking','amalgunivamalg #6','NULL',NULL,NULL,NULL,'NULL',858562.34,84
'Books','cooking','amalgunivamalg #8','NULL',NULL,NULL,NULL,'NULL',917692.00,74
'Books','cooking','amalgunivamalg #9','NULL',NULL,NULL,NULL,'NULL',881045.30,80
'Books','cooking','NULL','NULL',NULL,NULL,NULL,'NULL',5357549.49,16
'Books','entertainments','edu packmaxi #12','NULL',NULL,NULL,NULL,'NULL',962267.01,70
'Books','entertainments','edu packmaxi #2','NULL',NULL,NULL,NULL,'NULL',750249.81,94
'Books','entertainments','edu packmaxi #3','NULL',NULL,NULL,NULL,'NULL',1325140.63,39
'Books','entertainments','edu packmaxi #6','NULL',NULL,NULL,NULL,'NULL',874454.87,83
'Books','entertainments','edu packmaxi #8','NULL',NULL,NULL,NULL,'NULL',894416.62,78
'Books','entertainments','NULL','NULL',NULL,NULL,NULL,'NULL',5465761.48,14
'Books','fiction','scholarunivamalg #12','NULL',NULL,NULL,NULL,'NULL',957012.63,71
'Books','fiction','scholarunivamalg #2','NULL',NULL,NULL,NULL,'NULL',1798976.95,20
'Books','fiction','scholarunivamalg #3','NULL',NULL,NULL,NULL,'NULL',1288272.89,43
'Books','fiction','scholarunivamalg #6','NULL',NULL,NULL,NULL,'NULL',1031571.39,65
'Books','fiction','scholarunivamalg #8','NULL',NULL,NULL,NULL,'NULL',1444468.85,30
'Books','fiction','scholarunivamalg #9','NULL',NULL,NULL,NULL,'NULL',1333004.22,38
'Books','fiction','NULL','NULL',NULL,NULL,NULL,'NULL',7867516.44,3
'Books','history','scholarmaxi #12','NULL',NULL,NULL,NULL,'NULL',1407800.96,34
'Books','history','scholarmaxi #2','NULL',NULL,NULL,NULL,'NULL',1601550.87,25
'Books','history','scholarmaxi #3','NULL',NULL,NULL,NULL,'NULL',1181491.20,50
'Books','history','scholarmaxi #6','NULL',NULL,NULL,NULL,'NULL',1175904.65,51
'Books','history','scholarmaxi #8','NULL',NULL,NULL,NULL,'NULL',1205167.13,48
'Books','history','scholarmaxi #9','NULL',NULL,NULL,NULL,'NULL',1873407.99,18
'Books','history','NULL','NULL',NULL,NULL,NULL,'NULL',8458815.11,2
'Books','home repair','importounivamalg #12','NULL',NULL,NULL,NULL,'NULL',929871.46,72
'Books','home repair','importounivamalg #2','NULL',NULL,NULL,NULL,'NULL',1672201.56,22
'Books','home repair','importounivamalg #3','NULL',NULL,NULL,NULL,'NULL',1321000.94,40
'Books','home repair','importounivamalg #6','NULL',NULL,NULL,NULL,'NULL',717528.04,96
'Books','home repair','importounivamalg #8','NULL',NULL,NULL,NULL,'NULL',1669033.95,23
'Books','home repair','importounivamalg #9','NULL',NULL,NULL,NULL,'NULL',1066519.11,62
'Books','home repair','NULL','NULL',NULL,NULL,NULL,'NULL',7388154.74,4
'Books','mystery','corpunivamalg #12','NULL',NULL,NULL,NULL,'NULL',1146116.05,54
'Books','mystery','corpunivamalg #2','NULL',NULL,NULL,NULL,'NULL',747999.99,95
'Books','mystery','corpunivamalg #3','NULL',NULL,NULL,NULL,'NULL',1145257.96,55
'Books','mystery','corpunivamalg #6','NULL',NULL,NULL,NULL,'NULL',1098189.71,57
'Books','mystery','corpunivamalg #8','NULL',NULL,NULL,NULL,'NULL',759890.03,92
'Books','mystery','NULL','NULL',NULL,NULL,NULL,'NULL',5559086.76,13
'Books','parenting','corpmaxi #12','NULL',NULL,NULL,NULL,'NULL',1022503.60,66
'Books','parenting','corpmaxi #2','NULL',NULL,NULL,NULL,'NULL',908607.31,76
'Books','parenting','corpmaxi #6','NULL',NULL,NULL,NULL,'NULL',1717275.15,21
'Books','parenting','corpmaxi #9','NULL',NULL,NULL,NULL,'NULL',895829.36,77
'Books','parenting','NULL','NULL',NULL,NULL,NULL,'NULL',5905648.45,11
'Books','reference','brandmaxi #12','NULL',NULL,NULL,NULL,'NULL',1418372.80,33
'Books','reference','brandmaxi #2','NULL',NULL,NULL,NULL,'NULL',1154379.77,53
'Books','reference','brandmaxi #3','NULL',NULL,NULL,NULL,'NULL',1168210.15,52
'Books','reference','brandmaxi #6','NULL',NULL,NULL,NULL,'NULL',1349780.84,37
'Books','reference','brandmaxi #8','NULL',NULL,NULL,NULL,'NULL',1429502.89,31
'Books','reference','brandmaxi #9','NULL',NULL,NULL,NULL,'NULL',714094.43,97
'Books','reference','NULL','NULL',NULL,NULL,NULL,'NULL',7245435.64,6
'Books','romance','namelessmaxi #12','NULL',NULL,NULL,NULL,'NULL',1356568.45,36
'Books','romance','namelessmaxi #2','NULL',NULL,NULL,NULL,'NULL',1236630.16,47
'Books','romance','namelessmaxi #3','NULL',NULL,NULL,NULL,'NULL',1866764.20,19
'Books','romance','namelessmaxi #6','NULL',NULL,NULL,NULL,'NULL',1086707.92,59
'Books','romance','namelessmaxi #8','NULL',NULL,NULL,NULL,'NULL',825762.40,88
'Books','romance','namelessmaxi #9','NULL',NULL,NULL,NULL,'NULL',876869.83,81
'Books','romance','NULL','NULL',NULL,NULL,NULL,'NULL',7252274.41,5
'Books','science','maximaxi #2','NULL',NULL,NULL,NULL,'NULL',702171.30,98
'Books','science','maximaxi #3','NULL',NULL,NULL,NULL,'NULL',777021.93,90
'Books','science','maximaxi #6','NULL',NULL,NULL,NULL,'NULL',1446589.43,29
'Books','science','maximaxi #8','NULL',NULL,NULL,NULL,'NULL',1004042.04,67
'Books','science','maximaxi #9','NULL',NULL,NULL,NULL,'NULL',849422.20,85
'Books','science','NULL','NULL',NULL,NULL,NULL,'NULL',5359276.19,15
'Books','self-help','exportiunivamalg #2','NULL',NULL,NULL,NULL,'NULL',1283148.53,44
'Books','self-help','exportiunivamalg #3','NULL',NULL,NULL,NULL,'NULL',770181.59,91
'Books','self-help','exportiunivamalg #6','NULL',NULL,NULL,NULL,'NULL',1391623.86,35
'Books','self-help','exportiunivamalg #8','NULL',NULL,NULL,NULL,'NULL',695644.62,100
'Books','self-help','exportiunivamalg #9','NULL',NULL,NULL,NULL,'NULL',1427999.91,32
'Books','self-help','NULL','NULL',NULL,NULL,NULL,'NULL',6261649.72,8
'Books','sports','edu packunivamalg #12','NULL',NULL,NULL,NULL,'NULL',928301.94,73
'Books','sports','edu packunivamalg #6','NULL',NULL,NULL,NULL,'NULL',1503681.79,27
'Books','sports','edu packunivamalg #8','NULL',NULL,NULL,NULL,'NULL',1263974.84,46
'Books','sports','edu packunivamalg #9','NULL',NULL,NULL,NULL,'NULL',840684.29,87
'Books','sports','NULL','NULL',NULL,NULL,NULL,'NULL',5610649.06,12
'Books','travel','univunivamalg #12','NULL',NULL,NULL,NULL,'NULL',1135250.45,56
'Books','travel','univunivamalg #2','NULL',NULL,NULL,NULL,'NULL',1053533.86,64
'Books','travel','univunivamalg #3','NULL',NULL,NULL,NULL,'NULL',1085484.15,60
'Books','travel','univunivamalg #6','NULL',NULL,NULL,NULL,'NULL',696575.97,99
'Books','travel','univunivamalg #8','NULL',NULL,NULL,NULL,'NULL',1310411.80,41
'Books','travel','univunivamalg #9','NULL',NULL,NULL,NULL,'NULL',917487.24,75
'Books','travel','NULL','NULL',NULL,NULL,NULL,'NULL',6200882.35,9
'Books','NULL','NULL','NULL',NULL,NULL,NULL,'NULL',102246971.86,1
---- TYPES
STRING, STRING, STRING, STRING, INT, INT, INT, STRING, DECIMAL, BIGINT
====