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

     1  select
     2  	nation,
     3  	o_year,
     4  	sum(amount) as sum_profit
     5  from
     6  	(
     7  		select
     8  			n_name as nation,
     9  			extract(year from o_orderdate) as o_year,
    10  			l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
    11  		from
    12  			part,
    13  			supplier,
    14  			lineitem,
    15  			partsupp,
    16  			orders,
    17  			nation
    18  		where
    19  			s_suppkey = l_suppkey
    20  			and ps_suppkey = l_suppkey
    21  			and ps_partkey = l_partkey
    22  			and p_partkey = l_partkey
    23  			and o_orderkey = l_orderkey
    24  			and s_nationkey = n_nationkey
    25  			and p_name like '%pink%'
    26  	) as profit
    27  group by
    28  	nation,
    29  	o_year
    30  order by
    31  	nation,
    32  	o_year desc
    33  ;