-- $Id: q21-tpch07.sql 2657 2007-06-12 16:08:15Z rdempsey $
-- 21st query in 100 GB stream0 (tpch07).
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 = 'CANADA' AND N2.N_NAME = 'ROMANIA')
				OR	(N1.N_NAME = 'ROMANIA' AND N2.N_NAME = 'CANADA')
				) 
				AND L_SHIPDATE BETWEEN '1995-01-01' AND '1996-12-31'
		) SHIPPING
GROUP BY 
	SUPP_NATION,
	CUST_NATION,
	L_YEAR
ORDER BY 
	SUPP_NATION,
	CUST_NATION,
	L_YEAR;