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