I have SNOWFLAKE database table with customers and their transaction types:
customer_id type
1 a
1 a
2 b
2 b
2 b
3 a
3 b
3 a
At output I need distinct customer id and type of transaction:
customer_id type
1 a
2 b
3 mixed
My code does not work:
SELECT
"customer_id",
CASE WHEN type = 'a' THEN "a"
WHEN type = 'b' THEN "b"
ELSE "mixed" END OVER (PARTITION BY "customer_id" ) AS "type"
FROM CUSTOMERS
You need aggregation and a CASE expression that checks the number of distinct types of each customer_id:
SELECT customer_id,
CASE WHEN COUNT(DISTINCT type) = 1 THEN MAX(type) ELSE 'mixed' END AS type
FROM CUSTOMERS
GROUP BY customer_id;
Another way to write the CASE expression would be:
CASE WHEN MIN(type) = MAX(type) THEN MAX(type) ELSE 'mixed' END AS type
I assume that type is not nullable.
If the hardcoded "mixed" is not a must then LISTAGG could be used:
SELECT customer_id, LISTAGG(DISTINCT type, ',') AS type
FROM customer
GROUP BY cutomer_id;