BEGIN;
BEGIN
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 - l_discount) as volume
		from
			supplier,
			lineitem,
			orders,
			customer,
			nation n1,
			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 = 'MOZAMBIQUE' and n2.n_name = 'UNITED KINGDOM')
				or (n1.n_name = 'UNITED KINGDOM' and n2.n_name = 'MOZAMBIQUE')
			)
			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;
        supp_nation        |        cust_nation        | l_year |     revenue      
---------------------------+---------------------------+--------+------------------
 MOZAMBIQUE                | UNITED KINGDOM            |   1995 | 519184381.532021
 MOZAMBIQUE                | UNITED KINGDOM            |   1996 | 526618542.080058
 UNITED KINGDOM            | MOZAMBIQUE                |   1995 |  531444704.63345
 UNITED KINGDOM            | MOZAMBIQUE                |   1996 | 526239767.785726
(4 rows)

COMMIT;
COMMIT