GBQ SQL: For each unique value in Column A, if value ‘X’ appears in Column B….then create new Column C with value ‘Y’, else ‘N’

I’m using Google Big Query Standard SQL. Trying to figure out how to write this code. I have this table.

A   B
1   20
1   20
1   10
2   20
2   30

For each unique value in Column A, if the Column B has a value of ’10’, then create a new Column C with value ‘Y’, else ‘N’.

Results should look like this.

A   B    C
1   20   Y
1   20   Y
1   10   Y
2   20   N
2   30   N

In this result, since ‘1’ has at least once instance of ’10’, all rows ‘1’ will be assigned a ‘Y’ value in new column C.

Answer

Just use case and window functions:

select t.*,
       (case when countif(b = 10) over (partition by a) > 0 then 'Y' else 'N' end) as c
from t;

BigQuery has full support for booleans, so why use 'Y' and 'N', when you can use:

select t.*,
       (countif(b = 10) over (partition by a) > 0) as c
from t;