Select and agregate variable for distinct id in SNOWFLAKE SQL-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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;