yql/essentials/tests/sql/suites/pg-tpch/q11.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

--!syntax_pg
--TPC-H Q11
-- ignore runonopt plan diff


select
ps_partkey, 
sum(ps_supplycost * ps_availqty::numeric) as value
from 
plato."partsupp", 
plato."supplier",
plato."nation"
where 
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'GERMANY'
group by 
ps_partkey having 
sum(ps_supplycost * ps_availqty::numeric) > (
select 
sum(ps_supplycost * ps_availqty::numeric) * 0.0001::numeric
from 
plato."partsupp", 
plato."supplier", 
plato."nation"
where 
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'GERMANY'
)
order by
value desc;