blob: cf5a5fd9fb66cf030b9ab28e22f2c6fdde7a393b (
plain) (
blame)
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
|
--!syntax_pg
--TPC-DS Q44
-- start query 1 in stream 0 using template ../query_templates/query44.tpl
select asceding.rnk, i1.i_product_name best_performing, i2.i_product_name worst_performing
from(select *
from (select item_sk,rank() over (order by rank_col asc) rnk
from (select ss_item_sk item_sk,avg(ss_net_profit) rank_col
from plato.store_sales ss1
where ss_store_sk = 2
group by ss_item_sk
having avg(ss_net_profit) > 0.9::numeric*(select avg(ss_net_profit) rank_col
from plato.store_sales
where ss_store_sk = 2
and ss_hdemo_sk is null
group by ss_store_sk))V1)V11
where rnk < 11) asceding,
(select *
from (select item_sk,rank() over (order by rank_col desc) rnk
from (select ss_item_sk item_sk,avg(ss_net_profit) rank_col
from plato.store_sales ss1
where ss_store_sk = 2
group by ss_item_sk
having avg(ss_net_profit) > 0.9::numeric*(select avg(ss_net_profit) rank_col
from plato.store_sales
where ss_store_sk = 2
and ss_hdemo_sk is null
group by ss_store_sk))V2)V21
where rnk < 11) descending,
plato.item i1,
plato.item i2
where asceding.rnk = descending.rnk
and i1.i_item_sk=asceding.item_sk
and i2.i_item_sk=descending.item_sk
order by asceding.rnk
limit 100;
-- end query 1 in stream 0 using template ../query_templates/query44.tpl
|
