You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
45 lines
783 B
45 lines
783 B
3 years ago
|
--begin q22
|
||
|
|
-- $ID$
|
||
|
|
-- TPC-H/TPC-R Global Sales Opportunity Query (Q22)
|
||
|
|
-- Functional Query Definition
|
||
|
|
-- Approved February 1998
|
||
|
|
:x
|
||
|
|
:o
|
||
|
|
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
|
||
|
|
(':1', ':2', ':3', ':4', ':5', ':6', ':7')
|
||
|
|
and c_acctbal > (
|
||
|
|
select
|
||
|
|
avg(c_acctbal)
|
||
|
|
from
|
||
|
|
tpch.customer
|
||
|
|
where
|
||
|
|
c_acctbal > 0.00
|
||
|
|
and substring(c_phone from 1 for 2) in
|
||
|
|
(':1', ':2', ':3', ':4', ':5', ':6', ':7')
|
||
|
|
)
|
||
|
|
and not exists (
|
||
|
|
select
|
||
|
|
*
|
||
|
|
from
|
||
|
|
tpch.orders
|
||
|
|
where
|
||
|
|
o_custkey = c_custkey
|
||
|
|
)
|
||
|
|
) as custsale
|
||
|
|
group by
|
||
|
|
cntrycode
|
||
|
|
order by
|
||
|
|
cntrycode;
|
||
|
|
--end q22
|