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

     1  select
     2  	supp_nation,
     3  	cust_nation,
     4  	l_year,
     5  	sum(volume) as revenue
     6  from
     7  	(
     8  		select
     9  			n1.n_name as supp_nation,
    10  			n2.n_name as cust_nation,
    11  			extract(year from l_shipdate) as l_year,
    12  			l_extendedprice * (1 - l_discount) as volume
    13  		from
    14  			supplier,
    15  			lineitem,
    16  			orders,
    17  			customer,
    18  			nation n1,
    19  			nation n2
    20  		where
    21  			s_suppkey = l_suppkey
    22  			and o_orderkey = l_orderkey
    23  			and c_custkey = o_custkey
    24  			and s_nationkey = n1.n_nationkey
    25  			and c_nationkey = n2.n_nationkey
    26  			and (
    27  				(n1.n_name = 'FRANCE' and n2.n_name = 'ARGENTINA')
    28  				or (n1.n_name = 'ARGENTINA' and n2.n_name = 'FRANCE')
    29  			)
    30  			and l_shipdate between date '1995-01-01' and date '1996-12-31'
    31  	) as shipping
    32  group by
    33  	supp_nation,
    34  	cust_nation,
    35  	l_year
    36  order by
    37  	supp_nation,
    38  	cust_nation,
    39  	l_year
    40  ;