]>
Commit | Line | Data |
---|---|---|
aee94f69 TL |
1 | -- start query 1 in stream 0 using template query53.tpl and seed 1819994127 |
2 | select * from | |
3 | (select i_manufact_id, | |
4 | sum(ss_sales_price) sum_sales, | |
5 | avg(sum(ss_sales_price)) over (partition by i_manufact_id) avg_quarterly_sales | |
6 | from item, store_sales, date_dim, store | |
7 | where ss_item_sk = i_item_sk and | |
8 | ss_sold_date_sk = d_date_sk and | |
9 | ss_store_sk = s_store_sk and | |
10 | d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11) and | |
11 | ((i_category in ('Books','Children','Electronics') and | |
12 | i_class in ('personal','portable','reference','self-help') and | |
13 | i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7', | |
14 | 'exportiunivamalg #9','scholaramalgamalg #9')) | |
15 | or(i_category in ('Women','Music','Men') and | |
16 | i_class in ('accessories','classical','fragrances','pants') and | |
17 | i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1', | |
18 | 'importoamalg #1'))) | |
19 | group by i_manufact_id, d_qoy ) tmp1 | |
20 | where case when avg_quarterly_sales > 0 | |
21 | then abs (sum_sales - avg_quarterly_sales)/ avg_quarterly_sales | |
22 | else null end > 0.1 | |
23 | order by avg_quarterly_sales, | |
24 | sum_sales, | |
25 | i_manufact_id | |
26 | limit 100; | |
27 | ||
28 | -- end query 1 in stream 0 using template query53.tpl |