BigQuery SQL: Conditionally Count Column Values (if only one code exists from list) While Grouping By Unique ID

Question

Let's say I have the following sample input table:

ID	Code
1234	323F
1234	327F
1234	328F
1234	333F
2234	327F
2234	342F
3314	327F
3314	357F

I want to create a new column called "Count". The "Count" column gets a 1 value if and only if across the entire table the unique ID has only one of the following codes (in the "Code" column) in the list below:

• 323F, 327F, 328F, 333F, 357F

Otherwise the ID gets a "Count" value of 0.

Given the rules above, my desired output table is below:

ID	Count
1234	0
2234	1
3314	0

How do I do this using BigQuery SQL?

Answer 1

Use below approach

select ID, 
  (
    select if(count(*) = 1, 1, 0)
    from unnest(Codes) as code
    join unnest(['323F', '327F', '328F', '333F', '357F']) as code
    using(code)
  ) as `count`
from (
  select ID, array_agg(distinct Code) Codes
  from your_table
  group by ID
)

if applied to sample data in your question, output is