TPC series - TPC-H Query 22 - Prefix-strings and anti-joins

Query 22

SELECT cntrycode,
       COUNT(*)       AS numcust,
       SUM(c_acctbal) AS totacctbal
FROM (SELECT SUBSTRING(c_phone FROM 1 FOR 2) AS cntrycode,
             c_acctbal
      FROM tpch.customer
      WHERE SUBSTRING(c_phone FROM 1 FOR 2) IN
            ('10', '11', '13', '21', '23', '26', '33')
        AND c_acctbal > (SELECT AVG(c_acctbal)
                         FROM tpch.customer
                         WHERE c_acctbal > 0.00
                           AND SUBSTRING(c_phone FROM 1 FOR 2) IN
                               ('10', '11', '13', '21', '23', '26', '33'))
        AND NOT EXISTS (SELECT *
                        FROM tpch.orders
                        WHERE o_custkey = c_custkey)) AS custsale
GROUP BY cntrycode
ORDER BY cntrycode;