mariadb-columnstore-engine/dbcon/doc/100GB_stream0/q08-tpch08.sql

-- $Id: q08-tpch08.sql 2657 2007-06-12 16:08:15Z rdempsey $
-- 8th query in 100 GB stream0 (tpch08).

SELECT 
	O_YEAR,
	SUM (CASE 
			WHEN NATION = 'ROMANIA'
			THEN VOLUME
			ELSE 0
	END) / SUM(VOLUME) AS MKT_SHARE
FROM 
	( 
		SELECT 
			extract (YEAR FROM O_ORDERDATE) AS O_YEAR,
			L_EXTENDEDPRICE * (1-L_DISCOUNT) AS VOLUME,
			N2.N_NAME AS NATION
		FROM 
			PART,
			SUPPLIER,
			LINEITEM,
			ORDERS,
			CUSTOMER,
			NATION N1,
			NATION N2,
			REGION
		WHERE 
			P_PARTKEY = L_PARTKEY AND
			S_SUPPKEY = L_SUPPKEY AND
			L_ORDERKEY = O_ORDERKEY AND
			O_CUSTKEY = C_CUSTKEY AND
			C_NATIONKEY =	N1.N_NATIONKEY AND
			N1.N_REGIONKEY = R_REGIONKEY AND
			R_NAME = 'EUROPE' AND
			S_NATIONKEY =	N2.N_NATIONKEY AND
			O_ORDERDATE BETWEEN '1995-	01-01' AND '1996-12-31' AND
			P_TYPE = 'LARGE BRUSHED BRASS'
	) ALL_NATIONS
GROUP BY 
	O_YEAR
ORDER BY 
	O_YEAR;