resources/tpch/q7.sql

0001 -- using default substitutions
0002
0003 select
0004         supp_nation,
0005         cust_nation,
0006         l_year,
0007         sum(volume) as revenue
0008 from
0009         (
0010                 select
0011                         n1.n_name as supp_nation,
0012                         n2.n_name as cust_nation,
0013                         year(l_shipdate) as l_year,
0014                         l_extendedprice * (1 - l_discount) as volume
0015                 from
0016                         supplier,
0017                         lineitem,
0018                         orders,
0019                         customer,
0020                         nation n1,
0021                         nation n2
0022                 where
0023                         s_suppkey = l_suppkey
0024                         and o_orderkey = l_orderkey
0025                         and c_custkey = o_custkey
0026                         and s_nationkey = n1.n_nationkey
0027                         and c_nationkey = n2.n_nationkey
0028                         and (
0029                                 (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
0030                                 or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
0031                         )
0032                         and l_shipdate between date '1995-01-01' and date '1996-12-31'
0033         ) as shipping
0034 group by
0035         supp_nation,
0036         cust_nation,
0037         l_year
0038 order by
0039         supp_nation,
0040         cust_nation,
0041         l_year