blob: 7db204c44f7b81e0318b6c9d4a99154c65161d94 (
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
|
--!syntax_pg
--TPC-H Q9
select
nation,
o_year,
sum(amount) as sum_profit
from (
select
n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1::numeric - l_discount) - ps_supplycost * l_quantity as amount
from
plato."part",
plato."supplier",
plato."lineitem",
plato."partsupp",
plato."orders",
plato."nation"
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%green%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc;
|
