0001 WITH ss AS
0002 (SELECT
0003 s_store_sk,
0004 sum(ss_ext_sales_price) AS sales,
0005 sum(ss_net_profit) AS profit
0006 FROM store_sales, date_dim, store
0007 WHERE ss_sold_date_sk = d_date_sk
0008 AND d_date BETWEEN cast('2000-08-03' AS DATE) AND
0009 (cast('2000-08-03' AS DATE) + INTERVAL 30 days)
0010 AND ss_store_sk = s_store_sk
0011 GROUP BY s_store_sk),
0012 sr AS
0013 (SELECT
0014 s_store_sk,
0015 sum(sr_return_amt) AS returns,
0016 sum(sr_net_loss) AS profit_loss
0017 FROM store_returns, date_dim, store
0018 WHERE sr_returned_date_sk = d_date_sk
0019 AND d_date BETWEEN cast('2000-08-03' AS DATE) AND
0020 (cast('2000-08-03' AS DATE) + INTERVAL 30 days)
0021 AND sr_store_sk = s_store_sk
0022 GROUP BY s_store_sk),
0023 cs AS
0024 (SELECT
0025 cs_call_center_sk,
0026 sum(cs_ext_sales_price) AS sales,
0027 sum(cs_net_profit) AS profit
0028 FROM catalog_sales, date_dim
0029 WHERE cs_sold_date_sk = d_date_sk
0030 AND d_date BETWEEN cast('2000-08-03' AS DATE) AND
0031 (cast('2000-08-03' AS DATE) + INTERVAL 30 days)
0032 GROUP BY cs_call_center_sk),
0033 cr AS
0034 (SELECT
0035 sum(cr_return_amount) AS returns,
0036 sum(cr_net_loss) AS profit_loss
0037 FROM catalog_returns, date_dim
0038 WHERE cr_returned_date_sk = d_date_sk
0039 AND d_date BETWEEN cast('2000-08-03' AS DATE) AND
0040 (cast('2000-08-03' AS DATE) + INTERVAL 30 days)),
0041 ws AS
0042 (SELECT
0043 wp_web_page_sk,
0044 sum(ws_ext_sales_price) AS sales,
0045 sum(ws_net_profit) AS profit
0046 FROM web_sales, date_dim, web_page
0047 WHERE ws_sold_date_sk = d_date_sk
0048 AND d_date BETWEEN cast('2000-08-03' AS DATE) AND
0049 (cast('2000-08-03' AS DATE) + INTERVAL 30 days)
0050 AND ws_web_page_sk = wp_web_page_sk
0051 GROUP BY wp_web_page_sk),
0052 wr AS
0053 (SELECT
0054 wp_web_page_sk,
0055 sum(wr_return_amt) AS returns,
0056 sum(wr_net_loss) AS profit_loss
0057 FROM web_returns, date_dim, web_page
0058 WHERE wr_returned_date_sk = d_date_sk
0059 AND d_date BETWEEN cast('2000-08-03' AS DATE) AND
0060 (cast('2000-08-03' AS DATE) + INTERVAL 30 days)
0061 AND wr_web_page_sk = wp_web_page_sk
0062 GROUP BY wp_web_page_sk)
0063 SELECT
0064 channel,
0065 id,
0066 sum(sales) AS sales,
0067 sum(returns) AS returns,
0068 sum(profit) AS profit
0069 FROM
0070 (SELECT
0071 'store channel' AS channel,
0072 ss.s_store_sk AS id,
0073 sales,
0074 coalesce(returns, 0) AS returns,
0075 (profit - coalesce(profit_loss, 0)) AS profit
0076 FROM ss
0077 LEFT JOIN sr
0078 ON ss.s_store_sk = sr.s_store_sk
0079 UNION ALL
0080 SELECT
0081 'catalog channel' AS channel,
0082 cs_call_center_sk AS id,
0083 sales,
0084 returns,
0085 (profit - profit_loss) AS profit
0086 FROM cs, cr
0087 UNION ALL
0088 SELECT
0089 'web channel' AS channel,
0090 ws.wp_web_page_sk AS id,
0091 sales,
0092 coalesce(returns, 0) returns,
0093 (profit - coalesce(profit_loss, 0)) AS profit
0094 FROM ws
0095 LEFT JOIN wr
0096 ON ws.wp_web_page_sk = wr.wp_web_page_sk
0097 ) x
0098 GROUP BY ROLLUP (channel, id)
0099 ORDER BY channel, id
0100 LIMIT 100
