resources/tpch/q8.sql

0001 -- using default substitutions
0002
0003 select
0004         o_year,
0005         sum(case
0006                 when nation = 'BRAZIL' then volume
0007                 else 0
0008         end) / sum(volume) as mkt_share
0009 from
0010         (
0011                 select
0012                         year(o_orderdate) as o_year,
0013                         l_extendedprice * (1 - l_discount) as volume,
0014                         n2.n_name as nation
0015                 from
0016                         part,
0017                         supplier,
0018                         lineitem,
0019                         orders,
0020                         customer,
0021                         nation n1,
0022                         nation n2,
0023                         region
0024                 where
0025                         p_partkey = l_partkey
0026                         and s_suppkey = l_suppkey
0027                         and l_orderkey = o_orderkey
0028                         and o_custkey = c_custkey
0029                         and c_nationkey = n1.n_nationkey
0030                         and n1.n_regionkey = r_regionkey
0031                         and r_name = 'AMERICA'
0032                         and s_nationkey = n2.n_nationkey
0033                         and o_orderdate between date '1995-01-01' and date '1996-12-31'
0034                         and p_type = 'ECONOMY ANODIZED STEEL'
0035         ) as all_nations
0036 group by
0037         o_year
0038 order by
0039         o_year