SQL Interview Question | 35 | #dataengineering | #dataanalyst | #powerbi | #sql #dataengineer

If you like this video
please like, share & subscribe and comment down below..
if you have any suggestion or doubt in this video.

Script:

create table products(product_id int)
create table customers(customer_id varchar(10),product_id int)

insert into products values(1)
insert into products values(2)
insert into products values(3)

insert into customers values('c1',1)
insert into customers values('c2',1)
insert into customers values('c2',2)
insert into customers values('c2',3)
insert into customers values('c3',1)
insert into customers values('c3',2)

[victor-]

with ct as (select CUSTOMER_ID, dense_rank()over(partition by CUSTOMER_ID order by PRODUCT_ID asc ) as rn from customers)
select CUSTOMER_ID from ct where rn = (select count(*) from products);

When the customer purchase the same product multiple time we can use this query

[akhilsingh]

My approach joins with Having:

select customer_id from customers c
left join products p on c.product_id = p.product_id
group by customer_id
having(count(p.product_id)) = 3

[Sakthi-wj]

select customer_id from customers group by customer_id having count(*)=(select count(*) from products); i am wrirting this query considering without any duplication of products

[Hemaprabha-kxcj]

My solution
with cte as (
select customer_id, a.product_id as apro, ROW_NUMBER() OVER(PARTItiON BY CUSTOMER_id order by a.product_id) as rnk from cus a
join prodid b
on
a.product_id=b.product_id )
select customer_id from cte
group by customer_id
having COUNT(rnk)=3

[mallikarjunan]

What if a customer has purchased same product more than once

[sagarmoharir]

Select customer_id, count (*) from customers group by customer_id having count (product_id)>=3 please replay

[agentdatta]

SELECT customer_id
FROM customers
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*) FROM products);

[codewithvinay_cool]

select customer_id
from (
select customer_id, listagg(product_id, '||') within group(order by product_id) c
from customers
group by 1
)
where c in (
select listagg(product_id, '||') within group(order by product_id)
from products
)