1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
WITH sr_items AS
(SELECT
i_item_id item_id,
sum(sr_return_quantity) sr_item_qty
FROM store_returns, item, date_dim
WHERE sr_item_sk = i_item_sk
AND d_date IN (SELECT d_date
FROM date_dim
WHERE d_week_seq IN
(SELECT d_week_seq
FROM date_dim
WHERE d_date IN ('2000-06-30', '2000-09-27', '2000-11-17')))
AND sr_returned_date_sk = d_date_sk
GROUP BY i_item_id),
cr_items AS
(SELECT
i_item_id item_id,
sum(cr_return_quantity) cr_item_qty
FROM catalog_returns, item, date_dim
WHERE cr_item_sk = i_item_sk
AND d_date IN (SELECT d_date
FROM date_dim
WHERE d_week_seq IN
(SELECT d_week_seq
FROM date_dim
WHERE d_date IN ('2000-06-30', '2000-09-27', '2000-11-17')))
AND cr_returned_date_sk = d_date_sk
GROUP BY i_item_id),
wr_items AS
(SELECT
i_item_id item_id,
sum(wr_return_quantity) wr_item_qty
FROM web_returns, item, date_dim
WHERE wr_item_sk = i_item_sk AND d_date IN
(SELECT d_date
FROM date_dim
WHERE d_week_seq IN
(SELECT d_week_seq
FROM date_dim
WHERE d_date IN ('2000-06-30', '2000-09-27', '2000-11-17')))
AND wr_returned_date_sk = d_date_sk
GROUP BY i_item_id)
SELECT
sr_items.item_id,
sr_item_qty,
sr_item_qty / (sr_item_qty + cr_item_qty + wr_item_qty) / 3.0 * 100 sr_dev,
cr_item_qty,
cr_item_qty / (sr_item_qty + cr_item_qty + wr_item_qty) / 3.0 * 100 cr_dev,
wr_item_qty,
wr_item_qty / (sr_item_qty + cr_item_qty + wr_item_qty) / 3.0 * 100 wr_dev,
(sr_item_qty + cr_item_qty + wr_item_qty) / 3.0 average
FROM sr_items, cr_items, wr_items
WHERE sr_items.item_id = cr_items.item_id
AND sr_items.item_id = wr_items.item_id
ORDER BY sr_items.item_id, sr_item_qty
LIMIT 100
|
