====
---- QUERY: TPCDS-Q14-1
with  cross_items as
 (select i_item_sk ss_item_sk
 from item,
 (select iss.i_brand_id brand_id
     ,iss.i_class_id class_id
     ,iss.i_category_id category_id
 from store_sales
     ,item iss
     ,date_dim d1
 where ss_item_sk = iss.i_item_sk
   and ss_sold_date_sk = d1.d_date_sk
   and d1.d_year between 1999 AND 1999 + 2
 intersect 
 select ics.i_brand_id
     ,ics.i_class_id
     ,ics.i_category_id
 from catalog_sales
     ,item ics
     ,date_dim d2
 where cs_item_sk = ics.i_item_sk
   and cs_sold_date_sk = d2.d_date_sk
   and d2.d_year between 1999 AND 1999 + 2
 intersect
 select iws.i_brand_id
     ,iws.i_class_id
     ,iws.i_category_id
 from web_sales
     ,item iws
     ,date_dim d3
 where ws_item_sk = iws.i_item_sk
   and ws_sold_date_sk = d3.d_date_sk
   and d3.d_year between 1999 AND 1999 + 2) t1
 where i_brand_id = brand_id
      and i_class_id = class_id
      and i_category_id = category_id
),
 avg_sales as
 (select avg(quantity*list_price) average_sales
  from (select ss_quantity quantity
             ,ss_list_price list_price
       from store_sales
           ,date_dim
       where ss_sold_date_sk = d_date_sk
         and d_year between 1999 and 1999 + 2
       union all 
       select cs_quantity quantity 
             ,cs_list_price list_price
       from catalog_sales
           ,date_dim
       where cs_sold_date_sk = d_date_sk
         and d_year between 1999 and 1999 + 2 
       union all
       select ws_quantity quantity
             ,ws_list_price list_price
       from web_sales
           ,date_dim
       where ws_sold_date_sk = d_date_sk
         and d_year between 1999 and 1999 + 2) x)
 select channel, i_brand_id,i_class_id,i_category_id,sum(sales), sum(number_sales)
 from(
       select 'store' channel, i_brand_id,i_class_id
             ,i_category_id,sum(ss_quantity*ss_list_price) sales
             , count(*) number_sales
       from store_sales
           ,item
           ,date_dim
       where ss_item_sk in (select ss_item_sk from cross_items)
         and ss_item_sk = i_item_sk
         and ss_sold_date_sk = d_date_sk
         and d_year = 1999+2 
         and d_moy = 11
       group by i_brand_id,i_class_id,i_category_id
       having sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales)
       union all
       select 'catalog' channel, i_brand_id,i_class_id,i_category_id, sum(cs_quantity*cs_list_price) sales, count(*) number_sales
       from catalog_sales
           ,item
           ,date_dim
       where cs_item_sk in (select ss_item_sk from cross_items)
         and cs_item_sk = i_item_sk
         and cs_sold_date_sk = d_date_sk
         and d_year = 1999+2 
         and d_moy = 11
       group by i_brand_id,i_class_id,i_category_id
       having sum(cs_quantity*cs_list_price) > (select average_sales from avg_sales)
       union all
       select 'web' channel, i_brand_id,i_class_id,i_category_id, sum(ws_quantity*ws_list_price) sales , count(*) number_sales
       from web_sales
           ,item
           ,date_dim
       where ws_item_sk in (select ss_item_sk from cross_items)
         and ws_item_sk = i_item_sk
         and ws_sold_date_sk = d_date_sk
         and d_year = 1999+2
         and d_moy = 11
       group by i_brand_id,i_class_id,i_category_id
       having sum(ws_quantity*ws_list_price) > (select average_sales from avg_sales)
 ) y
 group by rollup (channel, i_brand_id,i_class_id,i_category_id)
 order by channel,i_brand_id,i_class_id,i_category_id
LIMIT 100
---- RESULTS
'catalog',1001001,1,1,115019.61,20
'catalog',1001001,1,2,146344.47,27
'catalog',1001001,1,3,22597.19,3
'catalog',1001001,1,4,107555.43,23
'catalog',1001001,1,5,122521.31,25
'catalog',1001001,1,6,16883.97,3
'catalog',1001001,1,7,46329.78,9
'catalog',1001001,1,8,77861.85,13
'catalog',1001001,1,9,99985.35,21
'catalog',1001001,1,10,100105.28,23
'catalog',1001001,1,NULL,855204.24,167
'catalog',1001001,2,2,43967.97,7
'catalog',1001001,2,3,68565.38,14
'catalog',1001001,2,5,12633.87,3
'catalog',1001001,2,NULL,125167.22,24
'catalog',1001001,3,1,11100.79,5
'catalog',1001001,3,2,60551.64,14
'catalog',1001001,3,4,28455.23,4
'catalog',1001001,3,6,36821.61,7
'catalog',1001001,3,7,17250.82,6
'catalog',1001001,3,8,14426.92,4
'catalog',1001001,3,9,30078.07,3
'catalog',1001001,3,NULL,198685.08,43
'catalog',1001001,4,2,45473.85,13
'catalog',1001001,4,3,16558.92,8
'catalog',1001001,4,4,47553.20,10
'catalog',1001001,4,NULL,109585.97,31
'catalog',1001001,5,9,30112.11,12
'catalog',1001001,5,10,29678.50,5
'catalog',1001001,5,NULL,59790.61,17
'catalog',1001001,6,9,10261.82,3
'catalog',1001001,6,NULL,10261.82,3
'catalog',1001001,7,7,18244.94,3
'catalog',1001001,7,NULL,18244.94,3
'catalog',1001001,8,7,28872.49,7
'catalog',1001001,8,10,26895.97,6
'catalog',1001001,8,NULL,55768.46,13
'catalog',1001001,9,6,30944.19,5
'catalog',1001001,9,NULL,30944.19,5
'catalog',1001001,11,9,82810.87,12
'catalog',1001001,11,NULL,82810.87,12
'catalog',1001001,12,10,38427.52,9
'catalog',1001001,12,NULL,38427.52,9
'catalog',1001001,15,9,53508.79,7
'catalog',1001001,15,10,59329.31,13
'catalog',1001001,15,NULL,112838.10,20
'catalog',1001001,NULL,NULL,1697729.02,347
'catalog',1001002,1,1,2673969.89,530
'catalog',1001002,1,NULL,2673969.89,530
'catalog',1001002,2,1,140831.91,29
'catalog',1001002,2,NULL,140831.91,29
'catalog',1001002,3,1,320175.87,67
'catalog',1001002,3,NULL,320175.87,67
'catalog',1001002,4,1,133287.96,21
'catalog',1001002,4,NULL,133287.96,21
'catalog',1001002,5,1,16606.90,9
'catalog',1001002,5,NULL,16606.90,9
'catalog',1001002,6,1,15133.01,4
'catalog',1001002,6,NULL,15133.01,4
'catalog',1001002,7,1,24471.26,10
'catalog',1001002,7,NULL,24471.26,10
'catalog',1001002,8,1,63773.05,12
'catalog',1001002,8,NULL,63773.05,12
'catalog',1001002,9,1,9167.19,3
'catalog',1001002,9,NULL,9167.19,3
'catalog',1001002,12,1,29108.42,7
'catalog',1001002,12,NULL,29108.42,7
'catalog',1001002,15,1,31143.45,6
'catalog',1001002,15,NULL,31143.45,6
'catalog',1001002,16,1,70162.42,8
'catalog',1001002,16,NULL,70162.42,8
'catalog',1001002,NULL,NULL,3527831.33,706
'catalog',1002001,1,1,76392.13,14
'catalog',1002001,1,2,118394.33,21
'catalog',1002001,1,4,29395.79,5
'catalog',1002001,1,5,35541.97,4
'catalog',1002001,1,6,26104.36,3
'catalog',1002001,1,9,18793.97,4
'catalog',1002001,1,10,44071.42,4
'catalog',1002001,1,NULL,348693.97,55
'catalog',1002001,2,1,239511.02,51
'catalog',1002001,2,2,147993.14,26
'catalog',1002001,2,3,100086.93,17
'catalog',1002001,2,4,53524.42,13
'catalog',1002001,2,5,48494.06,10
'catalog',1002001,2,6,142857.04,20
'catalog',1002001,2,7,116557.98,16
'catalog',1002001,2,8,92743.93,24
'catalog',1002001,2,9,203943.99,38
'catalog',1002001,2,10,88249.19,10
'catalog',1002001,2,NULL,1233961.70,225
'catalog',1002001,3,2,25171.13,6
'catalog',1002001,3,7,27766.70,3
'catalog',1002001,3,8,38116.49,8
'catalog',1002001,3,NULL,91054.32,17
'catalog',1002001,4,1,66896.68,15
'catalog',1002001,4,3,43672.63,7
'catalog',1002001,4,4,41393.52,6
'catalog',1002001,4,5,30464.86,4
'catalog',1002001,4,NULL,182427.69,32
---- TYPES
STRING, INT, INT, INT, DECIMAL, BIGINT
====