hq22.sql

-- Copyright (c) 2020, Oracle and/or its affiliates.
-- Licensed under the Universal Permissive License v 1.0 as shown at https://oss.oracle.com/licenses/upl/

-- Coypright (c) 2020, Transaction Processing Performance Council

-- TPC-H Query 22 - Global Sales Opprtunity
SELECT    
    CNTRYCODE, COUNT(*) AS NUMCUST, SUM(C_ACCTBAL) AS TOTACCTBAL
FROM
    (SELECT 
        SUBSTRING(C_PHONE FROM 1 FOR 2) AS CNTRYCODE, C_ACCTBAL
    FROM
        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
                CUSTOMER
            WHERE
                C_ACCTBAL > 0.00
                AND SUBSTRING(C_PHONE FROM 1 FOR 2) IN ('13' , '31', '23', '29', '30', '18', '17'))
        AND NOT EXISTS( SELECT 
                *
            FROM
                ORDERS
            WHERE
                O_CUSTKEY = C_CUSTKEY)) AS CUSTSALE
GROUP BY CNTRYCODE
ORDER BY CNTRYCODE;