How do I display the customer_id of customers who bought products A and B, but didn’t buy product C, ordered by ascending customer ID.
I tried the below code, but does not give me any result.
select customer_id, product_name from orders where customer_id = 'A' and 'product_name '= 'B'
select customer_id from orders where product_name = 'A'
intersect
select customer_id from orders where product_name = 'B'
except
select customer_id from orders where product_name = 'C'
intersect would handle any duplicates. Nice!
A ∩ B \ C using set algebra notation :) But another question: how it is efficient for real data?You can use analytical function as follows:
Select * from
(select customer_id, product_name ,
Count(distinct case when product_name in ('A','B') then product_name end)
Over (partition by customer_id) as cntab ,
Count(case when product_name = 'C' then product_name end)
Over (partition by customer_id) as cntc
from orders t) t
Where cntab = 2 and cntc = 0;
One method use exists and not exists:
select o.*
from orders o
where exists (select 1
from orders o2
where o2.customer_id = o.customer_id and o2.product_name = 'A'
) and
exists (select 1
from orders o2
where o2.customer_id = o.customer_id and o2.product_name = 'B'
) and
not exists (select 1
from orders o2
where o2.customer_id = o.customer_id and o2.product_name = 'C'
)
order by customer_id;
To me, this query reads well ...
SELECT customer_id
FROM (
SELECT TableA.customer_id
FROM (
SELECT customer_id
FROM orders
WHERE product_name = 'A'
) AS TableA
INNER JOIN (
SELECT customer_id
FROM orders
WHERE product_name = 'B'
) AS TableB
ON TableA.customer_id = TableB.customer_id
) AS TableX
WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE product_name = 'C')
Explanation. Get a list of customer ids that bought products A and B with self inner join. Then, pare that list down by removing any rows where those customers bought product C.