====
---- QUERY: TPCDS-Q77A
-- RESULT MISMATCH FROM ORIGINAL
-- FIXED. TAKE ACTUAL RESULT AS EXPECTED
with ss as
 (select s_store_sk,
         sum(ss_ext_sales_price) as sales,
         sum(ss_net_profit) as profit
 from store_sales,
      date_dim,
      store
 where ss_sold_date_sk = d_date_sk
       and cast(d_date as timestamp) between cast('2000-08-23' as timestamp)
                  and (cast('2000-08-23' as timestamp) + interval 30 days)
       and ss_store_sk = s_store_sk
 group by s_store_sk)
 ,
 sr as
 (select s_store_sk,
         sum(sr_return_amt) as return_amt,
         sum(sr_net_loss) as profit_loss
 from store_returns,
      date_dim,
      store
 where sr_returned_date_sk = d_date_sk
       and cast(d_date as timestamp) between cast('2000-08-23' as timestamp)
                  and (cast('2000-08-23' as timestamp) + interval 30 days)
       and sr_store_sk = s_store_sk
 group by s_store_sk),
 cs as
 (select cs_call_center_sk,
        sum(cs_ext_sales_price) as sales,
        sum(cs_net_profit) as profit
 from catalog_sales,
      date_dim
 where cs_sold_date_sk = d_date_sk
       and cast(d_date as timestamp) between cast('2000-08-23' as timestamp)
                  and (cast('2000-08-23' as timestamp) + interval 30 days)
 group by cs_call_center_sk
 ),
 cr as
 (select cr_call_center_sk,
         sum(cr_return_amount) as return_amt,
         sum(cr_net_loss) as profit_loss
 from catalog_returns,
      date_dim
 where cr_returned_date_sk = d_date_sk
       and cast(d_date as timestamp) between cast('2000-08-23' as timestamp)
                  and (cast('2000-08-23' as timestamp) + interval 30 days)
 group by cr_call_center_sk
 ),
 ws as
 ( select wp_web_page_sk,
        sum(ws_ext_sales_price) as sales,
        sum(ws_net_profit) as profit
 from web_sales,
      date_dim,
      web_page
 where ws_sold_date_sk = d_date_sk
       and cast(d_date as timestamp) between cast('2000-08-23' as timestamp)
                  and (cast('2000-08-23' as timestamp) + interval 30 days)
       and ws_web_page_sk = wp_web_page_sk
 group by wp_web_page_sk),
 wr as
 (select wp_web_page_sk,
        sum(wr_return_amt) as return_amt,
        sum(wr_net_loss) as profit_loss
 from web_returns,
      date_dim,
      web_page
 where wr_returned_date_sk = d_date_sk
       and cast(d_date as timestamp) between cast('2000-08-23' as timestamp)
                  and (cast('2000-08-23' as timestamp) + interval 30 days)
       and wr_web_page_sk = wp_web_page_sk
 group by wp_web_page_sk)
 ,
 results as
 (select channel
        , id
        , sum(sales) as sales
        , sum(return_amt) as return_amt
        , sum(profit) as profit
 from
 (select 'store channel' as channel
        , ss.s_store_sk as id
        , sales
        , coalesce(return_amt, 0) as return_amt
        , (profit - coalesce(profit_loss,0)) as profit
 from   ss left join sr
        on  ss.s_store_sk = sr.s_store_sk
 union all
 select 'catalog channel' as channel
        , cs_call_center_sk as id
        , sales
        , return_amt
        , (profit - profit_loss) as profit
 from  cs
       , cr
 union all
 select 'web channel' as channel
        , ws.wp_web_page_sk as id
        , sales
        , coalesce(return_amt, 0) return_amt
        , (profit - coalesce(profit_loss,0)) as profit
 from   ws left join wr
        on  ws.wp_web_page_sk = wr.wp_web_page_sk
 ) x
 group by channel, id )
  select  *
 from (
 select channel, id, sales, return_amt, profit from  results
 union
 select channel, NULL AS id, sum(sales) as sales, sum(return_amt) as return_amt, sum(profit) as profit from  results group by channel
 union
 select NULL AS channel, NULL AS id, sum(sales) as sales, sum(return_amt) as return_amt, sum(profit) as profit from  results
) foo
order by channel, id
 limit 100;
---- RESULTS: VERIFY_IS_EQUAL_SORTED
'catalog channel',1,132885061.65,2050279.74,-12674076.58
'catalog channel',2,140503047.65,2050279.74,-14906564.08
'catalog channel',5,130483795.90,2050279.74,-13798294.43
'catalog channel',NULL,538912.55,2050279.74,-1383554.73
'catalog channel',NULL,404410817.75,8201118.96,-42762489.82
'store channel',1,20390161.35,562762.31,-9133254.67
'store channel',2,19807085.95,539649.43,-8817821.00
'store channel',4,19599593.20,557973.00,-8389920.41
'store channel',7,19480205.51,520479.41,-8861241.78
'store channel',8,18636331.60,472731.69,-8409599.72
'store channel',10,19335995.71,519959.15,-8771453.62
'store channel',NULL,117249373.32,3173554.99,-52383291.20
'web channel',1,1226811.57,28406.98,-227375.53
'web channel',2,1191229.91,99179.48,-264992.86
'web channel',5,1467083.19,21625.36,-147366.78
'web channel',7,1343208.21,67708.76,-200969.21
'web channel',8,1262065.97,46749.46,-271001.70
'web channel',11,1425934.76,10034.84,-84693.54
'web channel',13,1335813.60,62142.91,-218022.02
'web channel',14,1469352.58,50742.65,-197789.09
'web channel',17,1219451.02,28732.85,-205497.30
'web channel',19,1343058.55,24108.59,-175397.06
'web channel',20,1511303.97,42538.28,-89439.28
'web channel',23,1409483.07,37116.42,-89855.78
'web channel',25,1370755.17,48916.38,-207512.02
'web channel',26,1465712.89,48072.56,-157007.72
'web channel',29,1407813.82,19233.11,-188381.47
'web channel',31,1369226.19,25494.42,-180972.91
'web channel',32,1166947.23,50731.53,-189061.60
'web channel',35,1400811.57,22363.43,-189390.67
'web channel',37,1407716.73,32534.27,-127244.28
'web channel',38,1444241.42,41815.25,-135372.36
'web channel',41,1492530.29,19599.96,-101110.49
'web channel',43,1343104.79,41175.01,-227340.10
'web channel',44,1416507.16,37134.99,-274620.10
'web channel',47,1449718.94,15989.92,-105473.72
'web channel',49,1414898.83,45004.31,-146020.31
'web channel',50,1319375.84,28284.26,-151036.44
'web channel',53,1389137.89,24570.34,-120694.61
'web channel',55,1463362.30,38157.61,-154431.83
'web channel',56,1355553.42,46633.14,-164174.45
'web channel',59,1395251.52,10683.93,-213197.75
'web channel',NULL,41277462.40,1115481.00,-5205442.98
'NULL',NULL,562937653.47,12490154.95,-100351224.00
---- TYPES
STRING, INT, DECIMAL, DECIMAL, DECIMAL
====