Microsoft SQL Interview Question | Find Supercloud Customer | SQL Window Function | Advanced SQL

[@badrilalnagar9232]

If a person gets to know the truth then waves of happiness start rising in his life.

[@ramad7153]

Please share the script to create the tables, it would be really helpful. Thanks for the video

[@amishasharma3211]

Will we get the same answer if we join the two tables with INNER JOIN?

[@iswaryanandini2171]

Hi Could you please share the scripts? Not sure if you are even reading comments. What's the use if you are not trying to help others solve this?

[@Team_Kanya_raasi]

My Approach : with cte as( select customer_id,count(distinct(product_category)) as count_cat from ( select customer_id,product_category from customer c join product p on p.product_id = c.product_id ) as x group by x.customer_id ) select distinct * from cte where count_cat = 3 Please correct me if I'm Wrong Thanks

[@rohigt5745]

It is MORE EFFICIENT to use INNER join instead of left. If a customer hasn't bought a product or category the inner join will filter it out right away & we don't want to include all customers. -------------------------------- With CTE as ( select P.product_category, C.customer_id FROM Products P inner join Customer_contracts C on P.product_id = C.product_id Group By P.product_category, C.customer_id ) Select customer_id FROM CTE Group by customer_id Having count(*) >= (select count(distinct) product_category from Products)

[@Ayush-vu6bl]

Mam are these type of questions asked in interviews for a job to freshers?

[@pavankumarreddy7866]

Yes

[@ajaycheryala7760]

Including a SQL script in the description could help you gain more subscribers.

[@hairavyadav6579]

My approach select customer_id from (select customer_id,count(distinct p.product_category) as cat from customer c left join products p on c.product_id = p.product_id group by 1) sal where cat = (select count(distinct product_category) from products);

[@user-gq6cg3ls7f]

with cte as( select customer_id, product_category, count(customer_id) over (partition by customer_id) cnt from customer_contracts c left join products_contracts p on c.product_id = p.product_id group by customer_id, product_category ) select distinct customer_id from cte where cnt=3

[@saikumarmogili9819]

My soln: with cte as ( select customer_id,product_category,count(distinct product_category) as uniq from customer_contracts c left join products p on p.product_id = c.product_id group by product_category,customer_id) select cte.customer_id from cte group by customer_id having sum(uniq) >=3

[@anirbanbiswas7624]

what you did,i also did the same after hearing the initial part & try to solve that out by myself...but check the end of video brother,it as asked to retrieve all the customers who purchased atleast 1 product from each category...

[@hairavyadav6579]

Please try to provide script

[@JeevanC-l3k]

with cte as ( select customer_id, product_category, count(*) over(partition by customer_id) as counts from customer_contracts as c join products as p on p.product_id = c.product_id group by customer_id, product_category ) select customer_id, product_category, product_name, amount from customer_contracts as c join products as p on p.product_id = c.product_id where customer_id in (select customer_id from cte where counts = 3);

[@tilu391]

simple soln: select c.customer_id from customer_Contracts as c join products as p on c.product_id=p.product_id group by c.customer_id having count(c.customer_id)>= (select count(*) from products);

[@Tech_with_Srini]

SELECT Customer_ID FROM Customer_Contracts WHERE Product_ID IN (SELECT Product_ID FROM Products) GROUP BY Customer_ID HAVING COUNT(DISTINCT Product_ID) = (SELECT COUNT(DISTINCT Product_ID) FROM Products);