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