0001 WITH ssr AS
0002 (SELECT
0003 s_store_id AS store_id,
0004 sum(ss_ext_sales_price) AS sales,
0005 sum(coalesce(sr_return_amt, 0)) AS returns,
0006 sum(ss_net_profit - coalesce(sr_net_loss, 0)) AS profit
0007 FROM store_sales
0008 LEFT OUTER JOIN store_returns ON
0009 (ss_item_sk = sr_item_sk AND
0010 ss_ticket_number = sr_ticket_number)
0011 ,
0012 date_dim, store, item, promotion
0013 WHERE ss_sold_date_sk = d_date_sk
0014 AND d_date BETWEEN cast('2000-08-23' AS DATE)
0015 AND (cast('2000-08-23' AS DATE) + INTERVAL 30 days)
0016 AND ss_store_sk = s_store_sk
0017 AND ss_item_sk = i_item_sk
0018 AND i_current_price > 50
0019 AND ss_promo_sk = p_promo_sk
0020 AND p_channel_tv = 'N'
0021 GROUP BY s_store_id),
0022 csr AS
0023 (SELECT
0024 cp_catalog_page_id AS catalog_page_id,
0025 sum(cs_ext_sales_price) AS sales,
0026 sum(coalesce(cr_return_amount, 0)) AS returns,
0027 sum(cs_net_profit - coalesce(cr_net_loss, 0)) AS profit
0028 FROM catalog_sales
0029 LEFT OUTER JOIN catalog_returns ON
0030 (cs_item_sk = cr_item_sk AND
0031 cs_order_number = cr_order_number)
0032 ,
0033 date_dim, catalog_page, item, promotion
0034 WHERE cs_sold_date_sk = d_date_sk
0035 AND d_date BETWEEN cast('2000-08-23' AS DATE)
0036 AND (cast('2000-08-23' AS DATE) + INTERVAL 30 days)
0037 AND cs_catalog_page_sk = cp_catalog_page_sk
0038 AND cs_item_sk = i_item_sk
0039 AND i_current_price > 50
0040 AND cs_promo_sk = p_promo_sk
0041 AND p_channel_tv = 'N'
0042 GROUP BY cp_catalog_page_id),
0043 wsr AS
0044 (SELECT
0045 web_site_id,
0046 sum(ws_ext_sales_price) AS sales,
0047 sum(coalesce(wr_return_amt, 0)) AS returns,
0048 sum(ws_net_profit - coalesce(wr_net_loss, 0)) AS profit
0049 FROM web_sales
0050 LEFT OUTER JOIN web_returns ON
0051 (ws_item_sk = wr_item_sk AND ws_order_number = wr_order_number)
0052 ,
0053 date_dim, web_site, item, promotion
0054 WHERE ws_sold_date_sk = d_date_sk
0055 AND d_date BETWEEN cast('2000-08-23' AS DATE)
0056 AND (cast('2000-08-23' AS DATE) + INTERVAL 30 days)
0057 AND ws_web_site_sk = web_site_sk
0058 AND ws_item_sk = i_item_sk
0059 AND i_current_price > 50
0060 AND ws_promo_sk = p_promo_sk
0061 AND p_channel_tv = 'N'
0062 GROUP BY web_site_id)
0063 SELECT
0064 channel,
0065 id,
0066 sum(sales) AS sales,
0067 sum(returns) AS returns,
0068 sum(profit) AS profit
0069 FROM (SELECT
0070 'store channel' AS channel,
0071 concat('store', store_id) AS id,
0072 sales,
0073 returns,
0074 profit
0075 FROM ssr
0076 UNION ALL
0077 SELECT
0078 'catalog channel' AS channel,
0079 concat('catalog_page', catalog_page_id) AS id,
0080 sales,
0081 returns,
0082 profit
0083 FROM csr
0084 UNION ALL
0085 SELECT
0086 'web channel' AS channel,
0087 concat('web_site', web_site_id) AS id,
0088 sales,
0089 returns,
0090 profit
0091 FROM wsr) x
0092 GROUP BY ROLLUP (channel, id)
0093 ORDER BY channel, id
0094 LIMIT 100
