-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com

-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

SELECT /*+ MONITOR NO_PARALLEL */

c.customer_id

, c.cust_first_name

, c.cust_last_name

, c.credit_limit

, o.order_mode

, avg(oi.unit_price)

FROM

soe.customers c

, soe.orders o

, soe.order_items oi

WHERE

-- join

c.customer_id = o.customer_id

AND o.order_id = oi.order_id

-- filter

AND o.order_mode = 'direct'

GROUP BY

c.customer_id

, c.cust_first_name

, c.cust_last_name

, c.credit_limit

, o.order_mode

HAVING

sum(oi.unit_price) > c.credit_limit * 100

/