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

     1  select
     2  	cntrycode,
     3  	count(*) as numcust,
     4  	sum(c_acctbal) as totacctbal
     5  from
     6  	(
     7  		select
     8  			substring(c_phone from 1 for 2) as cntrycode,
     9  			c_acctbal
    10  		from
    11  			customer
    12  		where
    13  			substring(c_phone from 1 for 2) in
    14  				('10', '11', '26', '22', '19', '20', '27')
    15  			and c_acctbal > (
    16  				select
    17  					avg(c_acctbal)
    18  				from
    19  					customer
    20  				where
    21  					c_acctbal > 0.00
    22  					and substring(c_phone from 1 for 2) in
    23  						('10', '11', '26', '22', '19', '20', '27')
    24  			)
    25  			and not exists (
    26  				select
    27  					*
    28  				from
    29  					orders
    30  				where
    31  					o_custkey = c_custkey
    32  			)
    33  	) as custsale
    34  group by
    35  	cntrycode
    36  order by
    37  	cntrycode
    38  ;