-- $Id: q20-tpch05.sql 2657 2007-06-12 16:08:15Z rdempsey $
-- 20th query in 100 GB stream0 (tpch05).

SELECT 
	N_NAME,
	SUM(L_EXTENDEDPRICE * (1-L_DISCOUNT)) AS REVENUE
FROM 
	CUSTOMER,
	ORDERS,
	LINEITEM,
	SUPPLIER,
	NATION,
	REGION
WHERE 
	C_CUSTKEY = O_CUSTKEY AND
	L_ORDERKEY = O_ORDERKEY AND
	L_SUPPKEY = S_SUPPKEY AND
	C_NATIONKEY = S_NATIONKEY AND
	S_NATIONKEY = N_NATIONKEY AND
	N_REGIONKEY = R_REGIONKEY AND
	R_NAME = 'AFRICA' AND
	O_ORDERDATE >= date '1997-01-01' AND
	O_ORDERDATE < date '1997-01-01' + interval '1' year
GROUP BY 
	N_NAME
ORDER BY 
	REVENUE DESC;