github.com/matrixorigin/matrixone@v1.2.0/pkg/sql/plan/tpch/q8.sql (about)

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