Calculating average price of items purchased by customers

Question

I have three tables: customer, order and line items. They are set up as follows:

CREATE TABLE cust_account(
cust_id DECIMAL(10) NOT NULL,
first VARCHAR(30),
last VARCHAR(30),
address VARCHAR(50),
PRIMARY KEY (cust_id));

CREATE TABLE orders(
order_num DECIMAL(10) NOT NULL,
cust_id DECIMAL(10) NOT NULL,
order_date DATE,
PRIMARY KEY (order_num));

CREATE TABLE lines(
order_num DECIMAL(10) NOT NULL,
line_id DECIMAL(10) NOT NULL,
item_num DECIMAL(10) NOT NULL,
price DECIMAL(10),
PRIMARY KEY (order_id, line_id),
FOREIGN KEY (item_id) REFERENCES products);

Using Oracle, I need to write a query that presents the average item price for for those customers that made more than 5 or more purchases. This is what I've been working with:

SELECT DISTINCT cust_account.cust_id,cust_account.first, cust_account.last, lines.AVG(price) AS average_price
FROM cust_account
 JOIN orders
 ON cust_account.cust_id = orders.cust_id
 JOIN lines
 ON lines.order_num = orders.order_num
 WHERE lines.item_num IN (SELECT lines.item_num
    FROM lines
    JOIN orders
    ON lines.order_num = orders.order_num
        GROUP BY lines.order_num
        HAVING COUNT(DISTINCT orders.cust_id) >= 5
    );

Answer 1

I think this is it. I don't think it will work right away (I know nothing about oracle) but I think you will get the idea:

SELECT orders.cust_id,
       AVG(lines.price) AS average_price
FROM lines
JOIN orders ON orders.order_num = orders.order_num
WHERE orders.cust_id IN (SELECT orders.cust_id
                         FROM orders
                         GROUP BY orders.cust_id
                         HAVING COUNT(*) >= 5)
GROUP BY orders.cust_id;

Subquery selects customers that have more than 5 orders. And main query just gets all lines from all orders made by this customers.

I guess you can eliminate subquery by using HAVING DISTINCT .... Anyways, one with subquery should work just fine.

UPD.

something like this

SELECT orders.cust_id,
       AVG(lines.price) AS average_price
JOIN orders ON orders.order_num = orders.order_num
GROUP BY orders.cust_id
HAVING COUNT(DISTINCT orders.id) >= 5;