| ==== |
| ---- QUERY: TPCDS-Q80A |
| with ssr as |
| (select s_store_id as store_id, |
| sum(ss_ext_sales_price) as sales, |
| sum(coalesce(sr_return_amt, 0)) as return_amt, |
| sum(ss_net_profit - coalesce(sr_net_loss, 0)) as profit |
| from store_sales left outer join store_returns on |
| (ss_item_sk = sr_item_sk and ss_ticket_number = sr_ticket_number), |
| date_dim, |
| store, |
| item, |
| promotion |
| where ss_sold_date_sk = d_date_sk |
| and d_date between cast('2000-08-23' as timestamp) |
| and (cast('2000-08-23' as timestamp) + interval 30 days ) |
| and ss_store_sk = s_store_sk |
| and ss_item_sk = i_item_sk |
| and i_current_price > 50 |
| and ss_promo_sk = p_promo_sk |
| and p_channel_tv = 'N' |
| group by s_store_id) |
| , |
| csr as |
| (select cp_catalog_page_id as catalog_page_id, |
| sum(cs_ext_sales_price) as sales, |
| sum(coalesce(cr_return_amount, 0)) as return_amt, |
| sum(cs_net_profit - coalesce(cr_net_loss, 0)) as profit |
| from catalog_sales left outer join catalog_returns on |
| (cs_item_sk = cr_item_sk and cs_order_number = cr_order_number), |
| date_dim, |
| catalog_page, |
| item, |
| promotion |
| where cs_sold_date_sk = d_date_sk |
| and d_date between cast('2000-08-23' as timestamp) |
| and (cast('2000-08-23' as timestamp) + interval 30 days ) |
| and cs_catalog_page_sk = cp_catalog_page_sk |
| and cs_item_sk = i_item_sk |
| and i_current_price > 50 |
| and cs_promo_sk = p_promo_sk |
| and p_channel_tv = 'N' |
| group by cp_catalog_page_id) |
| , |
| wsr as |
| (select web_site_id, |
| sum(ws_ext_sales_price) as sales, |
| sum(coalesce(wr_return_amt, 0)) as return_amt, |
| sum(ws_net_profit - coalesce(wr_net_loss, 0)) as profit |
| from web_sales left outer join web_returns on |
| (ws_item_sk = wr_item_sk and ws_order_number = wr_order_number), |
| date_dim, |
| web_site, |
| item, |
| promotion |
| where ws_sold_date_sk = d_date_sk |
| and d_date between cast('2000-08-23' as timestamp) |
| and (cast('2000-08-23' as timestamp) + interval 30 days ) |
| and ws_web_site_sk = web_site_sk |
| and ws_item_sk = i_item_sk |
| and i_current_price > 50 |
| and ws_promo_sk = p_promo_sk |
| and p_channel_tv = 'N' |
| group by web_site_id) |
| , |
| 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 |
| , concat('store', store_id) as id |
| , sales |
| , return_amt |
| , profit |
| from ssr |
| union all |
| select 'catalog channel' as channel |
| , concat('catalog_page', catalog_page_id) as id |
| , sales |
| , return_amt |
| , profit |
| from csr |
| union all |
| select 'web channel' as channel |
| , concat('web_site', web_site_id) as id |
| , sales |
| , return_amt |
| , profit |
| from wsr |
| ) x |
| group by channel, id) |
| |
| select channel |
| , id |
| , sales |
| , return_amt |
| , profit |
| 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 |
| 'catalog channel','catalog_pageAAAAAAAAAAABAAAA',19965.69,3132.48,-16011.54 |
| 'catalog channel','catalog_pageAAAAAAAAABABAAAA',14376.88,380.90,-3715.45 |
| 'catalog channel','catalog_pageAAAAAAAAACABAAAA',4427.58,0.00,-6520.01 |
| 'catalog channel','catalog_pageAAAAAAAAADABAAAA',8488.29,0.00,-3247.97 |
| 'catalog channel','catalog_pageAAAAAAAAADCBAAAA',16052.65,0.00,-5900.88 |
| 'catalog channel','catalog_pageAAAAAAAAAEABAAAA',10314.44,0.00,-7096.25 |
| 'catalog channel','catalog_pageAAAAAAAAAECBAAAA',2857.44,2548.08,-136.42 |
| 'catalog channel','catalog_pageAAAAAAAAAFABAAAA',5184.92,0.00,-507.04 |
| 'catalog channel','catalog_pageAAAAAAAAAFCBAAAA',594.44,0.00,-1518.44 |
| 'catalog channel','catalog_pageAAAAAAAAAGABAAAA',1107.03,190.82,-897.30 |
| 'catalog channel','catalog_pageAAAAAAAAAGCBAAAA',3059.65,0.00,-5088.38 |
| 'catalog channel','catalog_pageAAAAAAAAAHABAAAA',13134.30,0.00,2118.90 |
| 'catalog channel','catalog_pageAAAAAAAAAHCBAAAA',16181.17,0.00,4761.95 |
| 'catalog channel','catalog_pageAAAAAAAAAJCBAAAA',2448.69,0.00,557.49 |
| 'catalog channel','catalog_pageAAAAAAAAAKCBAAAA',1005.27,0.00,154.35 |
| 'catalog channel','catalog_pageAAAAAAAAAKPAAAAA',8445.19,0.00,-4147.63 |
| 'catalog channel','catalog_pageAAAAAAAAALCBAAAA',11511.46,0.00,1173.29 |
| 'catalog channel','catalog_pageAAAAAAAAALPAAAAA',22038.68,94.24,2354.44 |
| 'catalog channel','catalog_pageAAAAAAAAAMPAAAAA',24071.71,5664.86,-6484.42 |
| 'catalog channel','catalog_pageAAAAAAAAANCBAAAA',8352.06,0.00,4237.49 |
| 'catalog channel','catalog_pageAAAAAAAAANPAAAAA',14597.62,134.64,-15964.47 |
| 'catalog channel','catalog_pageAAAAAAAAAOCBAAAA',373.75,0.00,-1085.83 |
| 'catalog channel','catalog_pageAAAAAAAAAOPAAAAA',23808.03,0.00,5548.05 |
| 'catalog channel','catalog_pageAAAAAAAAAPCBAAAA',9531.26,0.00,581.49 |
| 'catalog channel','catalog_pageAAAAAAAAAPPAAAAA',33119.11,55.44,7393.54 |
| 'catalog channel','catalog_pageAAAAAAAABAABAAAA',4272.46,501.30,-9581.06 |
| 'catalog channel','catalog_pageAAAAAAAABBABAAAA',1080.28,0.00,-4305.79 |
| 'catalog channel','catalog_pageAAAAAAAABCABAAAA',33.90,0.00,-278.65 |
| 'catalog channel','catalog_pageAAAAAAAABDABAAAA',5872.30,88.38,-480.99 |
| 'catalog channel','catalog_pageAAAAAAAABDCBAAAA',5743.76,0.00,1462.64 |
| 'catalog channel','catalog_pageAAAAAAAABEABAAAA',2049.28,0.00,-4059.03 |
| 'catalog channel','catalog_pageAAAAAAAABECBAAAA',18997.60,0.00,-8490.71 |
| 'catalog channel','catalog_pageAAAAAAAABFABAAAA',31503.86,0.00,7136.80 |
| 'catalog channel','catalog_pageAAAAAAAABFCBAAAA',25421.67,8976.00,10129.52 |
| 'catalog channel','catalog_pageAAAAAAAABGABAAAA',10594.99,367.65,-2926.53 |
| 'catalog channel','catalog_pageAAAAAAAABHABAAAA',9000.33,1145.50,-12055.00 |
| 'catalog channel','catalog_pageAAAAAAAABICBAAAA',1094.54,0.00,-202.67 |
| 'catalog channel','catalog_pageAAAAAAAABJCBAAAA',2178.01,0.00,979.05 |
| 'catalog channel','catalog_pageAAAAAAAABKCBAAAA',9189.93,0.00,4352.16 |
| 'catalog channel','catalog_pageAAAAAAAABKPAAAAA',9637.79,1986.96,-19295.74 |
| 'catalog channel','catalog_pageAAAAAAAABLCBAAAA',13969.57,0.00,5111.08 |
| 'catalog channel','catalog_pageAAAAAAAABLPAAAAA',11655.71,3796.68,-15600.79 |
| 'catalog channel','catalog_pageAAAAAAAABMCBAAAA',11936.10,14.70,1200.76 |
| 'catalog channel','catalog_pageAAAAAAAABMPAAAAA',14866.48,0.00,-662.88 |
| 'catalog channel','catalog_pageAAAAAAAABNCBAAAA',5440.20,0.00,-3049.20 |
| 'catalog channel','catalog_pageAAAAAAAABNPAAAAA',4186.13,188.37,-16115.36 |
| 'catalog channel','catalog_pageAAAAAAAABOCBAAAA',3112.32,0.00,-2559.36 |
| 'catalog channel','catalog_pageAAAAAAAABOPAAAAA',2020.48,0.00,-192.04 |
| 'catalog channel','catalog_pageAAAAAAAABPCBAAAA',7148.02,0.00,1781.91 |
| 'catalog channel','catalog_pageAAAAAAAABPPAAAAA',17674.89,0.00,345.58 |
| 'catalog channel','catalog_pageAAAAAAAACAABAAAA',17255.36,0.00,-3926.28 |
| 'catalog channel','catalog_pageAAAAAAAACBABAAAA',2470.13,1545.21,-1544.35 |
| 'catalog channel','catalog_pageAAAAAAAACCABAAAA',6361.92,0.00,3790.08 |
| 'catalog channel','catalog_pageAAAAAAAACDCBAAAA',1526.49,0.00,-3403.89 |
| 'catalog channel','catalog_pageAAAAAAAACEABAAAA',10732.94,0.00,-984.47 |
| 'catalog channel','catalog_pageAAAAAAAACECBAAAA',648.84,0.00,170.04 |
| 'catalog channel','catalog_pageAAAAAAAACFABAAAA',6745.24,0.00,-3013.83 |
| 'catalog channel','catalog_pageAAAAAAAACGABAAAA',5143.41,0.00,1545.93 |
| 'catalog channel','catalog_pageAAAAAAAACGCBAAAA',1284.52,0.00,-144.08 |
| 'catalog channel','catalog_pageAAAAAAAACHABAAAA',10246.18,0.00,-6015.51 |
| 'catalog channel','catalog_pageAAAAAAAACHCBAAAA',10416.70,0.00,19.49 |
| 'catalog channel','catalog_pageAAAAAAAACICBAAAA',396.76,0.00,154.44 |
| 'catalog channel','catalog_pageAAAAAAAACJCBAAAA',281.26,0.00,-5252.32 |
| 'catalog channel','catalog_pageAAAAAAAACKCBAAAA',163.02,0.00,-46.83 |
| 'catalog channel','catalog_pageAAAAAAAACKPAAAAA',12857.24,730.99,-2517.04 |
| 'catalog channel','catalog_pageAAAAAAAACLCBAAAA',5470.63,0.00,1082.94 |
| 'catalog channel','catalog_pageAAAAAAAACLPAAAAA',21549.31,0.00,6695.34 |
| 'catalog channel','catalog_pageAAAAAAAACMCBAAAA',11329.50,0.00,5202.60 |
| 'catalog channel','catalog_pageAAAAAAAACMPAAAAA',7546.59,294.84,-2582.73 |
| 'catalog channel','catalog_pageAAAAAAAACNPAAAAA',23904.66,0.00,488.70 |
| 'catalog channel','catalog_pageAAAAAAAACOPAAAAA',19014.47,648.01,-8700.92 |
| 'catalog channel','catalog_pageAAAAAAAACPCBAAAA',1148.55,0.00,-932.52 |
| 'catalog channel','catalog_pageAAAAAAAACPPAAAAA',25230.53,0.00,-741.80 |
| 'catalog channel','catalog_pageAAAAAAAADAABAAAA',20305.27,0.00,972.58 |
| 'catalog channel','catalog_pageAAAAAAAADBABAAAA',1390.65,0.00,-4835.48 |
| 'catalog channel','catalog_pageAAAAAAAADCABAAAA',8296.22,4011.70,-5197.32 |
| 'catalog channel','catalog_pageAAAAAAAADDABAAAA',6289.02,0.00,1457.49 |
| 'catalog channel','catalog_pageAAAAAAAADDCBAAAA',14137.81,0.00,479.91 |
| 'catalog channel','catalog_pageAAAAAAAADEABAAAA',7572.00,77.80,2937.68 |
| 'catalog channel','catalog_pageAAAAAAAADECBAAAA',4207.12,0.00,-2457.02 |
| 'catalog channel','catalog_pageAAAAAAAADFABAAAA',23991.62,3.15,1216.83 |
| 'catalog channel','catalog_pageAAAAAAAADFCBAAAA',6537.84,0.00,-6173.37 |
| 'catalog channel','catalog_pageAAAAAAAADGABAAAA',9050.23,3062.68,-2935.52 |
| 'catalog channel','catalog_pageAAAAAAAADGCBAAAA',7789.42,0.00,-3868.94 |
| 'catalog channel','catalog_pageAAAAAAAADHABAAAA',25558.98,0.00,-6817.76 |
| 'catalog channel','catalog_pageAAAAAAAADHCBAAAA',5245.91,65.34,-1734.29 |
| 'catalog channel','catalog_pageAAAAAAAADICBAAAA',0.00,0.00,-2246.10 |
| 'catalog channel','catalog_pageAAAAAAAADJCBAAAA',7721.15,0.00,3313.85 |
| 'catalog channel','catalog_pageAAAAAAAADKCBAAAA',5463.32,1677.60,874.45 |
| 'catalog channel','catalog_pageAAAAAAAADKPAAAAA',30068.71,0.00,-8011.27 |
| 'catalog channel','catalog_pageAAAAAAAADLCBAAAA',1146.19,0.00,-1287.95 |
| 'catalog channel','catalog_pageAAAAAAAADLPAAAAA',35314.26,0.00,-5055.96 |
| 'catalog channel','catalog_pageAAAAAAAADMCBAAAA',2420.23,0.00,-1610.01 |
| 'catalog channel','catalog_pageAAAAAAAADMPAAAAA',32509.00,737.40,-14350.75 |
| 'catalog channel','catalog_pageAAAAAAAADNCBAAAA',9986.56,5534.72,1208.35 |
| 'catalog channel','catalog_pageAAAAAAAADNPAAAAA',1953.82,0.00,220.42 |
| 'catalog channel','catalog_pageAAAAAAAADOCBAAAA',3356.59,62.99,-4084.30 |
| 'catalog channel','catalog_pageAAAAAAAADOPAAAAA',5090.89,0.00,-2319.86 |
| 'catalog channel','catalog_pageAAAAAAAADPCBAAAA',7004.85,78.75,-1243.37 |
| 'catalog channel','catalog_pageAAAAAAAADPPAAAAA',22004.30,2309.27,3234.13 |
| ---- TYPES |
| STRING, STRING, DECIMAL, DECIMAL, DECIMAL |
| ==== |
