-- $Id: q03-tpch09.sql 2657 2007-06-12 16:08:15Z rdempsey $
-- Third query in 100 GB stream0 (tpch09).
SELECT 
	NATION,
	O_YEAR,
	SUM(AMOUNT) AS SUM_PROFIT
FROM 
	( 
		SELECT 
			N_NAME AS NATION,
			extract (YEAR FROM O_ORDERDATE) AS O_YEAR,
			L_EXTENDEDPRICE * (1-L_DISCOUNT) - PS_SUPPLYCOST * L_QUANTITY AS AMOUNT
		FROM 
			PART,
			SUPPLIER,
			LINEITEM,
			PARTSUPP,
			ORDERS,
			NATION
		WHERE 
			S_SUPPKEY = L_SUPPKEY AND
			PS_SUPPKEY = L_SUPPKEY AND
			PS_PARTKEY = L_PARTKEY AND
			P_PARTKEY = L_PARTKEY AND 
			O_ORDERKEY = L_ORDERKEY AND
			S_NATIONKEY = N_NATIONKEY AND
			P_NAME LIKE '%%orchid%%'
	) PROFIT
GROUP BY 
	NATION,
	O_YEAR
ORDER BY 
	NATION,
	O_YEAR DESC;