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

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