====
---- QUERY: TPCH-Q22
# Q22 - Global Sales Opportunity Query
select
  cntrycode,
  count(*) as numcust,
  sum(c_acctbal) as totacctbal
from (
  select
    substr(c_phone, 1, 2) as cntrycode,
    c_acctbal
  from
    customer c
  where
    substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17')
    and c_acctbal > (
      select
        avg(c_acctbal)
      from
        customer c
      where
        c_acctbal > 0.00
        and substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17')
      )
    and not exists (
      select
        o_orderkey
      from
        c.c_orders
    )
  ) as custsale
group by
  cntrycode
order by
  cntrycode
---- RESULTS
'13',888,6737713.99
'17',861,6460573.72
'18',964,7236687.40
'23',892,6701457.95
'29',948,7158866.63
'30',909,6808436.13
'31',922,6806670.18
---- TYPES
string, bigint, decimal
====