Create a Snowflake query -- 2

Hello,

I have a table that has 6 columns,

I need a query in snowflake that that:
1. Counts unique values in a column for each ID
2. Adds a value in a new column based on a condition.