text: -- $Id: tpch08.sql 3647 2007-12-13 21:20:41Z rdempsey $
  -- TPC-H/TPC-R National Market Share Query (Q8)
  -- Functional Query Definition
  -- Approved February 1998
  
  define 1 = BRAZIL
  define 2 = AMERICA
  define 3 = 'ECONOMY ANODIZED STEEL'
  
  select
  	o_year,
  	sum(case
  		when nation = '&1' 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 = '&2'
  			and s_nationkey = n2.n_nationkey
  			and o_orderdate between date '1995-01-01' and date '1996-12-31'
  			and p_type = '&3'
  	) all_nations
  group by
  	o_year
  order by
  	o_year;
  
  