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