-- tpch8 using 1395599672 as a seed to the RNG
select
  o_year,
  sum(case
    when nation = 'EGYPT' then volume
    else 0
  end) / sum(volume) as mkt_share
from
  (
    select
      extract(year from o.o_orderdate) as o_year,
      l.l_extendedprice * (1 - l.l_discount) as volume,
      n2.n_name as nation
    from
      cp.`tpch/part.parquet` p,
      cp.`tpch/supplier.parquet` s,
      cp.`tpch/lineitem.parquet` l,
      cp.`tpch/orders.parquet` o,
      cp.`tpch/customer.parquet` c,
      cp.`tpch/nation.parquet` n1,
      cp.`tpch/nation.parquet` n2,
      cp.`tpch/region.parquet` r
    where
      p.p_partkey = l.l_partkey
      and s.s_suppkey = l.l_suppkey
      and l.l_orderkey = o.o_orderkey
      and o.o_custkey = c.c_custkey
      and c.c_nationkey = n1.n_nationkey
      and n1.n_regionkey = r.r_regionkey
      and r.r_name = 'MIDDLE EAST'
      and s.s_nationkey = n2.n_nationkey
      and o.o_orderdate between date '1995-01-01' and date '1996-12-31'
      and p.p_type = 'PROMO BRUSHED COPPER'
  ) as all_nations
group by
  o_year
order by
  o_year;