0001 WITH sr_items AS
0002 (SELECT
0003 i_item_id item_id,
0004 sum(sr_return_quantity) sr_item_qty
0005 FROM store_returns, item, date_dim
0006 WHERE sr_item_sk = i_item_sk
0007 AND d_date IN (SELECT d_date
0008 FROM date_dim
0009 WHERE d_week_seq IN
0010 (SELECT d_week_seq
0011 FROM date_dim
0012 WHERE d_date IN ('2000-06-30', '2000-09-27', '2000-11-17')))
0013 AND sr_returned_date_sk = d_date_sk
0014 GROUP BY i_item_id),
0015 cr_items AS
0016 (SELECT
0017 i_item_id item_id,
0018 sum(cr_return_quantity) cr_item_qty
0019 FROM catalog_returns, item, date_dim
0020 WHERE cr_item_sk = i_item_sk
0021 AND d_date IN (SELECT d_date
0022 FROM date_dim
0023 WHERE d_week_seq IN
0024 (SELECT d_week_seq
0025 FROM date_dim
0026 WHERE d_date IN ('2000-06-30', '2000-09-27', '2000-11-17')))
0027 AND cr_returned_date_sk = d_date_sk
0028 GROUP BY i_item_id),
0029 wr_items AS
0030 (SELECT
0031 i_item_id item_id,
0032 sum(wr_return_quantity) wr_item_qty
0033 FROM web_returns, item, date_dim
0034 WHERE wr_item_sk = i_item_sk AND d_date IN
0035 (SELECT d_date
0036 FROM date_dim
0037 WHERE d_week_seq IN
0038 (SELECT d_week_seq
0039 FROM date_dim
0040 WHERE d_date IN ('2000-06-30', '2000-09-27', '2000-11-17')))
0041 AND wr_returned_date_sk = d_date_sk
0042 GROUP BY i_item_id)
0043 SELECT
0044 sr_items.item_id,
0045 sr_item_qty,
0046 sr_item_qty / (sr_item_qty + cr_item_qty + wr_item_qty) / 3.0 * 100 sr_dev,
0047 cr_item_qty,
0048 cr_item_qty / (sr_item_qty + cr_item_qty + wr_item_qty) / 3.0 * 100 cr_dev,
0049 wr_item_qty,
0050 wr_item_qty / (sr_item_qty + cr_item_qty + wr_item_qty) / 3.0 * 100 wr_dev,
0051 (sr_item_qty + cr_item_qty + wr_item_qty) / 3.0 average
0052 FROM sr_items, cr_items, wr_items
0053 WHERE sr_items.item_id = cr_items.item_id
0054 AND sr_items.item_id = wr_items.item_id
0055 ORDER BY sr_items.item_id, sr_item_qty
0056 LIMIT 100
