0001 WITH ss_items AS
0002 (SELECT
0003 i_item_id item_id,
0004 sum(ss_ext_sales_price) ss_item_rev
0005 FROM store_sales, item, date_dim
0006 WHERE ss_item_sk = i_item_sk
0007 AND d_date IN (SELECT d_date
0008 FROM date_dim
0009 WHERE d_week_seq = (SELECT d_week_seq
0010 FROM date_dim
0011 WHERE d_date = '2000-01-03'))
0012 AND ss_sold_date_sk = d_date_sk
0013 GROUP BY i_item_id),
0014 cs_items AS
0015 (SELECT
0016 i_item_id item_id,
0017 sum(cs_ext_sales_price) cs_item_rev
0018 FROM catalog_sales, item, date_dim
0019 WHERE cs_item_sk = i_item_sk
0020 AND d_date IN (SELECT d_date
0021 FROM date_dim
0022 WHERE d_week_seq = (SELECT d_week_seq
0023 FROM date_dim
0024 WHERE d_date = '2000-01-03'))
0025 AND cs_sold_date_sk = d_date_sk
0026 GROUP BY i_item_id),
0027 ws_items AS
0028 (SELECT
0029 i_item_id item_id,
0030 sum(ws_ext_sales_price) ws_item_rev
0031 FROM web_sales, item, date_dim
0032 WHERE ws_item_sk = i_item_sk
0033 AND d_date IN (SELECT d_date
0034 FROM date_dim
0035 WHERE d_week_seq = (SELECT d_week_seq
0036 FROM date_dim
0037 WHERE d_date = '2000-01-03'))
0038 AND ws_sold_date_sk = d_date_sk
0039 GROUP BY i_item_id)
0040 SELECT
0041 ss_items.item_id,
0042 ss_item_rev,
0043 ss_item_rev / (ss_item_rev + cs_item_rev + ws_item_rev) / 3 * 100 ss_dev,
0044 cs_item_rev,
0045 cs_item_rev / (ss_item_rev + cs_item_rev + ws_item_rev) / 3 * 100 cs_dev,
0046 ws_item_rev,
0047 ws_item_rev / (ss_item_rev + cs_item_rev + ws_item_rev) / 3 * 100 ws_dev,
0048 (ss_item_rev + cs_item_rev + ws_item_rev) / 3 average
0049 FROM ss_items, cs_items, ws_items
0050 WHERE ss_items.item_id = cs_items.item_id
0051 AND ss_items.item_id = ws_items.item_id
0052 AND ss_item_rev BETWEEN 0.9 * cs_item_rev AND 1.1 * cs_item_rev
0053 AND ss_item_rev BETWEEN 0.9 * ws_item_rev AND 1.1 * ws_item_rev
0054 AND cs_item_rev BETWEEN 0.9 * ss_item_rev AND 1.1 * ss_item_rev
0055 AND cs_item_rev BETWEEN 0.9 * ws_item_rev AND 1.1 * ws_item_rev
0056 AND ws_item_rev BETWEEN 0.9 * ss_item_rev AND 1.1 * ss_item_rev
0057 AND ws_item_rev BETWEEN 0.9 * cs_item_rev AND 1.1 * cs_item_rev
0058 ORDER BY item_id, ss_item_rev
0059 LIMIT 100
