Table of Customer attributes, customers who buy A, B, and both A+B

user_15102332 0
Viewed 57

I have a customer transaction table that looks similar to this:

Customer ID Product Purchased
A Red
B Blue
A Blue
C Red
B Blue

It contains all transactions over a long time period I want to know which customers have bought just red, just blue and both red and blue, so a table like this:

Customer ID Product Purchased
A Both
B Blue only
C Red only

I've attempted a CASE WHEN statement, but it ends up duplicating the customer IDs But when I group by customer ID then I get an error saying the case statement isn't aggregating

snowflake-cloud-data-platform sql
2 Answers

Conditional aggreagation could be used:

SELECT CustomerID,
     CASE WHEN COUNT(DISTINCT Product) > 1THEN 'Many'
          WHEN COUNT_IF(Product='Red') > 0 THEN 'Red only'
          WHEN COUNT_IF(Product='Blue') > 0 THEN 'Blue only'
     END
FROM tab
GROUP BY CustomerID;
user_50708790

So if you have multiple product values Lukasz solution work if you add a filter to exclude the other values, like so:

SELECT CustomerID,
     CASE WHEN COUNT(DISTINCT Product) = 2 THEN 'Both'
          WHEN COUNT_IF(Product='Red') > 0 THEN 'Red only'
          WHEN COUNT_IF(Product='Blue') > 0 THEN 'Blue only'
     END
FROM tab
WHERE Product in ('Red','Blue')
GROUP BY CustomerID;

otherwise a Orange would throw off the answer.

user_439920
Related