| ==== |
| ---- 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 |
| where |
| substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17') |
| and c_acctbal > ( |
| select |
| avg(c_acctbal) |
| from |
| customer |
| where |
| c_acctbal > 0.00 |
| and substr(c_phone, 1, 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 |
| ---- 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 |
| ==== |