How to flag whether a user follows a condition at least once?

user_15268236 0
Viewed 27

Here is what my following dataset looks like: enter image description here

I have multiple users who each have a user_key. FLAG_1 represents the website they have visited (A=Website 1, B=Website 2). I want to create a 2nd flag that denotes whether a user has visited Webiste 2 at least once. User 111 has not visited Website 2. His FLAG_2 is therefore 0 because his FLAG_1 is never =B. User 222 has visited Website 2 and his FLAG_2 is therefore 1 (FOR ALL INSTANCES IN THE TABLE). User 333 has only ever visited Website 2 and his FLAG_2 is therefore 1.

What kind of function should I use to flag whether a user has at least 1 "B" in his FLAG_1, and as a result, has a FLAG_2 always=1. I am thinking of using a CASE WHEN but is this the right approach?

Thank you!

snowflake-cloud-data-platform sql
1 Answers

You can use window functions:

select t.*,
       max(case when flag1 = 'B' then 1 else 0 end) over (partition by user) as ever_B
from t;

Or, as I prefer, use booleans:

select t.*,
       boolor_agg(flag1 = 'B') over (partition by user) as ever_b
from t;
user_11440350
Related