Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance. Project price only 1 $
You can buy this project and download/modify it how often you want.
WITH ssr AS (
SELECT s_store_id, sum(sales_price) AS sales, sum(profit) AS profit
, sum(return_amt) AS returns, sum(net_loss) AS profit_loss
FROM (
SELECT ss_store_sk AS store_sk, ss_sold_date_sk AS date_sk, ss_ext_sales_price AS sales_price, ss_net_profit AS profit, CAST(0 AS decimal(7, 2)) AS return_amt
, CAST(0 AS decimal(7, 2)) AS net_loss
FROM store_sales
UNION ALL
SELECT sr_store_sk AS store_sk, sr_returned_date_sk AS date_sk, CAST(0 AS decimal(7, 2)) AS sales_price, CAST(0 AS decimal(7, 2)) AS profit, sr_return_amt AS return_amt
, sr_net_loss AS net_loss
FROM store_returns
) salesreturns, date_dim, store
WHERE date_sk = d_date_sk
AND d_date BETWEEN CAST('2000-08-23' AS date) AND date_add(CAST('2000-08-23' AS date), 14)
AND store_sk = s_store_sk
GROUP BY s_store_id
),
csr AS (
SELECT cp_catalog_page_id, sum(sales_price) AS sales, sum(profit) AS profit
, sum(return_amt) AS returns, sum(net_loss) AS profit_loss
FROM (
SELECT cs_catalog_page_sk AS page_sk, cs_sold_date_sk AS date_sk, cs_ext_sales_price AS sales_price, cs_net_profit AS profit, CAST(0 AS decimal(7, 2)) AS return_amt
, CAST(0 AS decimal(7, 2)) AS net_loss
FROM catalog_sales
UNION ALL
SELECT cr_catalog_page_sk AS page_sk, cr_returned_date_sk AS date_sk, CAST(0 AS decimal(7, 2)) AS sales_price, CAST(0 AS decimal(7, 2)) AS profit, cr_return_amount AS return_amt
, cr_net_loss AS net_loss
FROM catalog_returns
) salesreturns, date_dim, catalog_page
WHERE date_sk = d_date_sk
AND d_date BETWEEN CAST('2000-08-23' AS date) AND date_add(CAST('2000-08-23' AS date), 14)
AND page_sk = cp_catalog_page_sk
GROUP BY cp_catalog_page_id
),
wsr AS (
SELECT web_site_id, sum(sales_price) AS sales, sum(profit) AS profit
, sum(return_amt) AS returns, sum(net_loss) AS profit_loss
FROM (
SELECT ws_web_site_sk AS wsr_web_site_sk, ws_sold_date_sk AS date_sk, ws_ext_sales_price AS sales_price, ws_net_profit AS profit, CAST(0 AS decimal(7, 2)) AS return_amt
, CAST(0 AS decimal(7, 2)) AS net_loss
FROM web_sales
UNION ALL
SELECT ws_web_site_sk AS wsr_web_site_sk, wr_returned_date_sk AS date_sk, CAST(0 AS decimal(7, 2)) AS sales_price, CAST(0 AS decimal(7, 2)) AS profit, wr_return_amt AS return_amt
, wr_net_loss AS net_loss
FROM web_returns
LEFT JOIN web_sales
ON wr_item_sk = ws_item_sk
AND wr_order_number = ws_order_number
) salesreturns, date_dim, web_site
WHERE date_sk = d_date_sk
AND d_date BETWEEN CAST('2000-08-23' AS date) AND date_add(CAST('2000-08-23' AS date), 14)
AND wsr_web_site_sk = web_site_sk
GROUP BY web_site_id
)
SELECT channel, id, sum(sales) AS sales
, sum(returns) AS returns, sum(profit) AS profit
FROM (
SELECT 'store channel' AS channel, concat('store', s_store_id) AS id, sales
, returns, profit - profit_loss AS profit
FROM ssr
UNION ALL
SELECT 'catalog channel' AS channel, concat('catalog_page', cp_catalog_page_id) AS id, sales
, returns, profit - profit_loss AS profit
FROM csr
UNION ALL
SELECT 'web channel' AS channel, concat('web_site', web_site_id) AS id, sales
, returns, profit - profit_loss AS profit
FROM wsr
) x
GROUP BY ROLLUP (channel, id)
ORDER BY channel, id
LIMIT 100
