0001 WITH ssr AS
0002 ( SELECT
0003 s_store_id,
0004 sum(sales_price) AS sales,
0005 sum(profit) AS profit,
0006 sum(return_amt) AS RETURNS,
0007 sum(net_loss) AS profit_loss
0008 FROM
0009 (SELECT
0010 ss_store_sk AS store_sk,
0011 ss_sold_date_sk AS date_sk,
0012 ss_ext_sales_price AS sales_price,
0013 ss_net_profit AS profit,
0014 cast(0 AS DECIMAL(7, 2)) AS return_amt,
0015 cast(0 AS DECIMAL(7, 2)) AS net_loss
0016 FROM store_sales
0017 UNION ALL
0018 SELECT
0019 sr_store_sk AS store_sk,
0020 sr_returned_date_sk AS date_sk,
0021 cast(0 AS DECIMAL(7, 2)) AS sales_price,
0022 cast(0 AS DECIMAL(7, 2)) AS profit,
0023 sr_return_amt AS return_amt,
0024 sr_net_loss AS net_loss
0025 FROM store_returns)
0026 salesreturns, date_dim, store
0027 WHERE date_sk = d_date_sk
0028 AND d_date BETWEEN cast('2000-08-23' AS DATE)
0029 AND ((cast('2000-08-23' AS DATE) + INTERVAL 14 days))
0030 AND store_sk = s_store_sk
0031 GROUP BY s_store_id),
0032 csr AS
0033 ( SELECT
0034 cp_catalog_page_id,
0035 sum(sales_price) AS sales,
0036 sum(profit) AS profit,
0037 sum(return_amt) AS RETURNS,
0038 sum(net_loss) AS profit_loss
0039 FROM
0040 (SELECT
0041 cs_catalog_page_sk AS page_sk,
0042 cs_sold_date_sk AS date_sk,
0043 cs_ext_sales_price AS sales_price,
0044 cs_net_profit AS profit,
0045 cast(0 AS DECIMAL(7, 2)) AS return_amt,
0046 cast(0 AS DECIMAL(7, 2)) AS net_loss
0047 FROM catalog_sales
0048 UNION ALL
0049 SELECT
0050 cr_catalog_page_sk AS page_sk,
0051 cr_returned_date_sk AS date_sk,
0052 cast(0 AS DECIMAL(7, 2)) AS sales_price,
0053 cast(0 AS DECIMAL(7, 2)) AS profit,
0054 cr_return_amount AS return_amt,
0055 cr_net_loss AS net_loss
0056 FROM catalog_returns
0057 ) salesreturns, date_dim, catalog_page
0058 WHERE date_sk = d_date_sk
0059 AND d_date BETWEEN cast('2000-08-23' AS DATE)
0060 AND ((cast('2000-08-23' AS DATE) + INTERVAL 14 days))
0061 AND page_sk = cp_catalog_page_sk
0062 GROUP BY cp_catalog_page_id)
0063 ,
0064 wsr AS
0065 ( SELECT
0066 web_site_id,
0067 sum(sales_price) AS sales,
0068 sum(profit) AS profit,
0069 sum(return_amt) AS RETURNS,
0070 sum(net_loss) AS profit_loss
0071 FROM
0072 (SELECT
0073 ws_web_site_sk AS wsr_web_site_sk,
0074 ws_sold_date_sk AS date_sk,
0075 ws_ext_sales_price AS sales_price,
0076 ws_net_profit AS profit,
0077 cast(0 AS DECIMAL(7, 2)) AS return_amt,
0078 cast(0 AS DECIMAL(7, 2)) AS net_loss
0079 FROM web_sales
0080 UNION ALL
0081 SELECT
0082 ws_web_site_sk AS wsr_web_site_sk,
0083 wr_returned_date_sk AS date_sk,
0084 cast(0 AS DECIMAL(7, 2)) AS sales_price,
0085 cast(0 AS DECIMAL(7, 2)) AS profit,
0086 wr_return_amt AS return_amt,
0087 wr_net_loss AS net_loss
0088 FROM web_returns
0089 LEFT OUTER JOIN web_sales ON
0090 (wr_item_sk = ws_item_sk
0091 AND wr_order_number = ws_order_number)
0092 ) salesreturns, date_dim, web_site
0093 WHERE date_sk = d_date_sk
0094 AND d_date BETWEEN cast('2000-08-23' AS DATE)
0095 AND ((cast('2000-08-23' AS DATE) + INTERVAL 14 days))
0096 AND wsr_web_site_sk = web_site_sk
0097 GROUP BY web_site_id)
0098 SELECT
0099 channel,
0100 id,
0101 sum(sales) AS sales,
0102 sum(returns) AS returns,
0103 sum(profit) AS profit
0104 FROM
0105 (SELECT
0106 'store channel' AS channel,
0107 concat('store', s_store_id) AS id,
0108 sales,
0109 returns,
0110 (profit - profit_loss) AS profit
0111 FROM ssr
0112 UNION ALL
0113 SELECT
0114 'catalog channel' AS channel,
0115 concat('catalog_page', cp_catalog_page_id) AS id,
0116 sales,
0117 returns,
0118 (profit - profit_loss) AS profit
0119 FROM csr
0120 UNION ALL
0121 SELECT
0122 'web channel' AS channel,
0123 concat('web_site', web_site_id) AS id,
0124 sales,
0125 returns,
0126 (profit - profit_loss) AS profit
0127 FROM wsr
0128 ) x
0129 GROUP BY ROLLUP (channel, id)
0130 ORDER BY channel, id
0131 LIMIT 100
