--!syntax_pg
--TPC-H Q22


select 
cntrycode, 
count(*) as numcust, 
sum(c_acctbal) as totacctbal
from (
select 
substring(c_phone from 1 for 2) as cntrycode, 
c_acctbal
from 
plato."customer"
where 
substring(c_phone from 1 for 2) in 
('13','31','23','29','30','18','17')
and c_acctbal > (
select 
avg(c_acctbal)
from 
plato."customer"
where 
c_acctbal > 0.00::numeric
and substring (c_phone from 1 for 2) in
('13','31','23','29','30','18','17')
)
and not exists (
select 
* 
from 
plato."orders"
where 
o_custkey = c_custkey
)
) as custsale
group by 
cntrycode 
order by 
cntrycode;