CODEWITHSUNDEEP
sqloracle-databasejoinselectsubquery
Kevin
Kevin

Reputation: 43

Oracle query for customers who buy popular products

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 line_it(
order_id DECIMAL(10) NOT NULL,
line_id DECIMAL(10) NOT NULL,
item_num DECIMAL(10) NOT NULL,
PRIMARY KEY (order_id, line_id),
FOREIGN KEY (item_id) REFERENCES products);

I need to write a query that selects customers, their names and addresses who have purchased items that have been bought by 3 or more people. I have the following query:

SELECT cust_account.cust_id, cust_account.first, cust_account.last, cust_account.address
FROM cust_account
INNER JOIN orders ON cust_account.cust_id = orders.cust_id
INNER JOIN line_it ON orders.order_id = line_it.order_id
GROUP BY cust_account.cust_id, cust_account.last
HAVING COUNT(line_it.item_num) = (
    SELECT COUNT (DISTINCT order_num > 3)
    FROM line_it
    );

Do I even need to make it a subquery? I am a bit lost. Appreciate any help, thanks.

Upvotes: 1

Views: 77

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Start with "items bought by 3 or more people". You can get these by doing:

select li.item_id
from line_item li join
     order_info oi
     on li.order_id = oi.order_id
group by li.item_id
having count(distinct oi.customer_id) >= 3;

Now you want customers in this set. Hmmmm:

select distinct ca.*
from customer_account ca join
     orderinfo oi
     on ca.customer_id = oi.customer_id join
     line_item li
     on li.order_id = oi.order_id
where li.item_id in (select li.item_id
                     from line_item li join
                          order_info oi
                          on li.order_id = oi.order_id
                     group by li.item_id
                     having count(distinct oi.customer_id) >= 3
                    );

You can also express this with window functions:

select distinct ca.*
from (select ca.*, count(distinct customer_id) over (partition by li.item_id) as num_customers_on_item
      from customer_account ca join
           orderinfo oi
           on ca.customer_id = oi.customer_id join
           line_item li
           on li.order_id = oi.order_id
     ) ca
where num_customers_on_item >= 3;

Upvotes: 2

Karthik
Karthik

Reputation: 99

You can use the following query

SELECT distinct customer_account.* FROM line_item, order_info ,customer_account  where item_id in (
--Selecting only item purchased 3 or more
SELECT item_id FROM  line_item  group by item_id  having count(1)  >=3
)
and line_item.order_id = order_info.order_id
and customer_account.customer_id = order_info.customer_id
;

Upvotes: 1

Related Questions