-
Notifications
You must be signed in to change notification settings - Fork 32
/
query63.sql
45 lines (45 loc) · 2.12 KB
/
query63.sql
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
-- start query 63 in stream 0 using template query63.tpl
SELECT *
FROM (SELECT i_manager_id,
Sum(ss_sales_price) sum_sales,
Avg(Sum(ss_sales_price))
OVER (
partition BY i_manager_id) avg_monthly_sales
FROM item,
store_sales,
date_dim,
store
WHERE ss_item_sk = i_item_sk
AND ss_sold_date_sk = d_date_sk
AND ss_store_sk = s_store_sk
AND d_month_seq IN ( 1200, 1200 + 1, 1200 + 2, 1200 + 3,
1200 + 4, 1200 + 5, 1200 + 6, 1200 + 7,
1200 + 8, 1200 + 9, 1200 + 10, 1200 + 11 )
AND ( ( i_category IN ( 'Books', 'Children', 'Electronics' )
AND i_class IN ( 'personal', 'portable', 'reference',
'self-help' )
AND i_brand IN ( 'scholaramalgamalg #14',
'scholaramalgamalg #7'
,
'exportiunivamalg #9',
'scholaramalgamalg #9' )
)
OR ( i_category IN ( 'Women', 'Music', 'Men' )
AND i_class IN ( 'accessories', 'classical',
'fragrances',
'pants' )
AND i_brand IN ( 'amalgimporto #1',
'edu packscholar #1',
'exportiimporto #1',
'importoamalg #1' ) ) )
GROUP BY i_manager_id,
d_moy) tmp1
WHERE CASE
WHEN avg_monthly_sales > 0 THEN Abs (sum_sales - avg_monthly_sales) /
avg_monthly_sales
ELSE NULL
END > 0.1
ORDER BY i_manager_id,
avg_monthly_sales,
sum_sales
LIMIT 100;