ydb/yt/yql/tests/sql/suites/pg-tpch/q07.sql

--!syntax_pg
--TPC-H Q7


select
supp_nation, 
cust_nation, 
l_year, sum(volume) as revenue
from (
select 
n1.n_name as supp_nation, 
n2.n_name as cust_nation, 
extract(year from l_shipdate) as l_year,
l_extendedprice * (1::numeric - l_discount) as volume
from 
plato."supplier", 
plato."lineitem", 
plato."orders", 
plato."customer", 
plato."nation" n1, 
plato."nation" n2
where 
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and (
(n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
)
and l_shipdate between date '1995-01-01' and date '1996-12-31'
) as shipping
group by 
supp_nation, 
cust_nation, 
l_year
order by 
supp_nation, 
cust_nation, 
l_year;