1-- $Id: q08-tpch08.sql 2657 2007-06-12 16:08:15Z rdempsey $
2-- 8th query in 100 GB stream0 (tpch08).
3
4SELECT
5	O_YEAR,
6	SUM (CASE
7			WHEN NATION = 'ROMANIA'
8			THEN VOLUME
9			ELSE 0
10	END) / SUM(VOLUME) AS MKT_SHARE
11FROM
12	(
13		SELECT
14			extract (YEAR FROM O_ORDERDATE) AS O_YEAR,
15			L_EXTENDEDPRICE * (1-L_DISCOUNT) AS VOLUME,
16			N2.N_NAME AS NATION
17		FROM
18			PART,
19			SUPPLIER,
20			LINEITEM,
21			ORDERS,
22			CUSTOMER,
23			NATION N1,
24			NATION N2,
25			REGION
26		WHERE
27			P_PARTKEY = L_PARTKEY AND
28			S_SUPPKEY = L_SUPPKEY AND
29			L_ORDERKEY = O_ORDERKEY AND
30			O_CUSTKEY = C_CUSTKEY AND
31			C_NATIONKEY =	N1.N_NATIONKEY AND
32			N1.N_REGIONKEY = R_REGIONKEY AND
33			R_NAME = 'EUROPE' AND
34			S_NATIONKEY =	N2.N_NATIONKEY AND
35			O_ORDERDATE BETWEEN '1995-	01-01' AND '1996-12-31' AND
36			P_TYPE = 'LARGE BRUSHED BRASS'
37	) ALL_NATIONS
38GROUP BY
39	O_YEAR
40ORDER BY
41	O_YEAR;
42
43