How to use SQL to label each row based on certain criteria?

I need to correctly label each row based on certain criteria. For example the data I have is like this: Table Product

product_id product_name category
123        Egg          A
456        Egg          B
456        Milk         B
789        Milk         A
135        Apple        B
137        Orange       B
137        Banana       B
139        Strawberry   B

Since product ID 123 Egg is found in product ID 456, product 123 is labeled as A. The same is true with product ID 789 Milk. Since product ID 456 has no product names the same as others (since it has two), it is unique and then labeled as B.

I was trying to use array functions and compare them, but it doesn't work for this scenario. For example,

select product_id,array_agg(product_name) as p1 from product 

Then compare p1 with another array (p2) from the self inner join.

Any hints or help would be greatly appreciated!

from Recent Questions - Stack Overflow https://ift.tt/3GhbQPh
https://ift.tt/eA8V8J