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
57
58
59
60
61
62
63
64
65
|
-- start query 1 in stream 0 using template query58.tpl and seed 1819994127
with ss_items as
(select i_item_id item_id
,sum(ss_ext_sales_price) ss_item_rev
from store_sales
,item
,date_dim
where ss_item_sk = i_item_sk
and d_date in (select d_date
from date_dim
where d_week_seq = (select d_week_seq
from date_dim
where d_date = cast('1998-02-21' as date)))
and ss_sold_date_sk = d_date_sk
group by i_item_id),
cs_items as
(select i_item_id item_id
,sum(cs_ext_sales_price) cs_item_rev
from catalog_sales
,item
,date_dim
where cs_item_sk = i_item_sk
and d_date in (select d_date
from date_dim
where d_week_seq = (select d_week_seq
from date_dim
where d_date = cast('1998-02-21' as date)))
and cs_sold_date_sk = d_date_sk
group by i_item_id),
ws_items as
(select i_item_id item_id
,sum(ws_ext_sales_price) ws_item_rev
from web_sales
,item
,date_dim
where ws_item_sk = i_item_sk
and d_date in (select d_date
from date_dim
where d_week_seq =(select d_week_seq
from date_dim
where d_date = cast('1998-02-21' as date)))
and ws_sold_date_sk = d_date_sk
group by i_item_id)
select ss_items.item_id
,ss_item_rev
,ss_item_rev/((ss_item_rev+cs_item_rev+ws_item_rev)/3) * 100 ss_dev
,cs_item_rev
,cs_item_rev/((ss_item_rev+cs_item_rev+ws_item_rev)/3) * 100 cs_dev
,ws_item_rev
,ws_item_rev/((ss_item_rev+cs_item_rev+ws_item_rev)/3) * 100 ws_dev
,(ss_item_rev+cs_item_rev+ws_item_rev)/3 average
from ss_items,cs_items,ws_items
where ss_items.item_id=cs_items.item_id
and ss_items.item_id=ws_items.item_id
and ss_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev
and ss_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev
and cs_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev
and cs_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev
and ws_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev
and ws_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev
order by item_id
,ss_item_rev
limit 100;
-- end query 1 in stream 0 using template query58.tpl
|
