How to get the customers who has multiple product values

Mark 0
Viewed 50

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?

ansi-sql snowflake-cloud-data-platform sql
3 Answers

Using QUALIFY and windowed COUNT:

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

This is the solution for mysql, you can modify and make it compatible with ansi-sql

SELECT table1.* FROM table1
JOIN
    (SELECT table1.cust, COUNT(table1.prod) FROM table1
    GROUP BY cust
    HAVING COUNT(table1.prod) > 1)
as a ON a.cust = table1.cust

Check out this db fiddle

learning0

You could also use an uncorrelated subquery

select *
from t
where cust in (select cust
                from t
                group by cust
                having count(distinct prod)>1)
Phil Coulson0
Related