1-- $Id: q03-tpch09.sql 2657 2007-06-12 16:08:15Z rdempsey $
2-- Third query in 100 GB stream0 (tpch09).
3SELECT
4	NATION,
5	O_YEAR,
6	SUM(AMOUNT) AS SUM_PROFIT
7FROM
8	(
9		SELECT
10			N_NAME AS NATION,
11			extract (YEAR FROM O_ORDERDATE) AS O_YEAR,
12			L_EXTENDEDPRICE * (1-L_DISCOUNT) - PS_SUPPLYCOST * L_QUANTITY AS AMOUNT
13		FROM
14			PART,
15			SUPPLIER,
16			LINEITEM,
17			PARTSUPP,
18			ORDERS,
19			NATION
20		WHERE
21			S_SUPPKEY = L_SUPPKEY AND
22			PS_SUPPKEY = L_SUPPKEY AND
23			PS_PARTKEY = L_PARTKEY AND
24			P_PARTKEY = L_PARTKEY AND
25			O_ORDERKEY = L_ORDERKEY AND
26			S_NATIONKEY = N_NATIONKEY AND
27			P_NAME LIKE '%%orchid%%'
28	) PROFIT
29GROUP BY
30	NATION,
31	O_YEAR
32ORDER BY
33	NATION,
34	O_YEAR DESC;
35
36