How to get the customers who has multiple product values

Question

I have a table with 2 columns table(cust, prod)

Below is the table

cust                prod
1100000000104440000 PRODUCT_1
1100000000083280000 PRODUCT_1
1100000000104440000 PRODUCT_2
1000000000000950000 PRODUCT_2

I'm trying to get the customer who bought more than one product. The expected output must be

cust                prod
1100000000104440000 PRODUCT_1
1100000000104440000 PRODUCT_2

I wrote below SQL query, but it's not working

WHERE table.prod IN ('PRODUCT_1', 'PRODUCT_2')

Can anyone help me with this?

Answer 1

Using QUALIFY and windowed COUNT:

SELECT *
FROM table
QUALIFY COUNT(DISTINCT prod) OVER(PARTITION BY cust) > 1;