0001
0002
0003 select
0004 cntrycode,
0005 count(*) as numcust,
0006 sum(c_acctbal) as totacctbal
0007 from
0008 (
0009 select
0010 substring(c_phone, 1, 2) as cntrycode,
0011 c_acctbal
0012 from
0013 customer
0014 where
0015 substring(c_phone, 1, 2) in
0016 ('13', '31', '23', '29', '30', '18', '17')
0017 and c_acctbal > (
0018 select
0019 avg(c_acctbal)
0020 from
0021 customer
0022 where
0023 c_acctbal > 0.00
0024 and substring(c_phone, 1, 2) in
0025 ('13', '31', '23', '29', '30', '18', '17')
0026 )
0027 and not exists (
0028 select
0029 *
0030 from
0031 orders
0032 where
0033 o_custkey = c_custkey
0034 )
0035 ) as custsale
0036 group by
0037 cntrycode
0038 order by
0039 cntrycode