Find customers who bought product Milk, Chocolate, and products of category Electronics and Gardenin-CodePudding

I have two tables

sales (sales_id, product_id, customer_id)
product (product_id, category)

I want to get all customer_id's who had these products in their basket.

Product with product_id 10 and 20 together
Products with category 'Gardening'
Products with category 'Electronics'

This is my SQL query to get the the customers who bought 10 and 20 together but I can't figure out how to combine these with category 'Dairy' and 'Electronic' together

  SELECT customer_id FROM (
    SELECT customer_id, sales_id, COUNT(sales_id) OVER (PARTITION BY sales_id) as no_items
    FROM sales
    WHERE product_in IN ('10', '20')
  ) as t
  where t.no_items = 2

Table Sales

sales_id  product_id  customer_id
1         10          A
1         20          A
1         30          A
1         40          A
2         50          B

Table Product

product_id  category 
10          Dairy
20          Toys & Games
30          Electronics
40          Gardening
50          Apparel

Expected Output

customer_id
A

CodePudding user response：

Using aggregation we can try:

SELECT s.customer_id
FROM sales s
INNER JOIN product p ON p.product_id = s.product_id
WHERE s.product_id IN (10, 20) AND
      p.category IN ('Gardening', 'Electronics')
GROUP BY s.customer_id
HAVING COUNT(DISTINCT s.product_id) = 2 AND
       COUNT(DISTINCT p.category) = 2;

CodePudding user response：

Group by customer_id having count in your conditions more then 0 (notice I used category_ with an underscore sign just delete that _ to make the code work for your tables):

      SELECT customer_id
      FROM   sales s, product p
      WHERE  p.product_id=s.product_id
      group by customer_id
      having COUNT(CASE s.product_id WHEN 10 THEN 1 END)>0 
      and    COUNT(CASE s.product_id WHEN 20 THEN 1 END)>0
      and    COUNT(CASE p.category_ WHEN 'Gardening' THEN 1 END)>0
      and    COUNT(CASE p.category_ WHEN 'Electronics' THEN 1 END)>0

This sample data:

create table sales (sales_id number, product_id number, customer_id varchar2(4));
create table product (product_id number, category_ varchar2(50));

insert into sales values(1,         10     ,     'A');
insert into sales values(1,         20      ,    'A');
insert into sales values(1 ,        30    ,      'A');
insert into sales values(1  ,       40   ,       'A');
insert into sales values(5   ,      50  ,        'B');


insert into product values(10          ,'Dairy');
insert into product values(20          ,'Toys_and_Games');
insert into product values(30         , 'Electronics');
insert into product values(40         , 'Gardening');
insert into product values(50        ,  'Apparel');

RESULT:

CUSTOMER_ID
-----------
A

CodePudding user response：

You must join the tables and group by sales_id and customer_id because you want to apply your conditions on the same basket:

SELECT DISTINCT s.customer_id
FROM sales s INNER JOIN product p 
ON p.product_id = s.product_id
GROUP BY sales_id, s.customer_id 
HAVING COUNT(DISTINCT CASE WHEN s.product_id IN (10, 20) THEN s.product_id END) = 2 
   AND COUNT(DISTINCT CASE WHEN p.category IN ('Gardening', 'Electronics') THEN p.category END) = 2;

Assuming that for the same sales_id there is only 1 customer_id, you could also group by only by sales_id and the query would be:

SELECT MAX(s.customer_id) customer_id
FROM sales s INNER JOIN product p 
ON p.product_id = s.product_id
GROUP BY sales_id
HAVING COUNT(DISTINCT CASE WHEN s.product_id IN (10, 20) THEN s.product_id END) = 2 
   AND COUNT(DISTINCT CASE WHEN p.category IN ('Gardening', 'Electronics') THEN p.category END) = 2;

See the demo.