github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/benchmark/tpch/03_QUERIES/q8.sql (about)

     1  use tpch;
     2  select
     3      o_year,
     4      (sum(case
     5          when nation = 'ARGENTINA' then volume
     6          else 0
     7      end) / sum(volume)) as mkt_share
     8  from
     9      (
    10          select
    11              extract(year from o_orderdate) as o_year,
    12              l_extendedprice * (1 - l_discount) as volume,
    13              n2.n_name as nation
    14          from
    15              part,
    16              supplier,
    17              lineitem,
    18              orders,
    19              customer,
    20              nation n1,
    21              nation n2,
    22              region
    23          where
    24              p_partkey = l_partkey
    25              and s_suppkey = l_suppkey
    26              and l_orderkey = o_orderkey
    27              and o_custkey = c_custkey
    28              and c_nationkey = n1.n_nationkey
    29              and n1.n_regionkey = r_regionkey
    30              and r_name = 'AMERICA'
    31              and s_nationkey = n2.n_nationkey
    32              and o_orderdate between date '1995-01-01' and date '1996-12-31'
    33              and p_type = 'ECONOMY BURNISHED TIN'
    34      ) as all_nations
    35  group by
    36      o_year
    37  order by
    38      o_year
    39  ;