Iterating over a column in Big query-CodePudding

I am using Big query to extract results from employee learning table. I need to figure out if an employee has completed a set of courses for a department. For example if an employee complete all of these three courses say, course 100, course 200 and course 300, they can be classified as as compliant else They are non-compliant. I have created a dummy example of how my data is structured, unfortunately due to organization policy I cant share more information.

Employee course 
1        100 
1        101 
1        200 
1        300
1        300 
1        400 
2        100 
2        200
3        100
3        200
3        300
4        75
4        85
4        95
4        105 
4        115
4        125  
5        200
5        200
5        100
5        100
5        100
5        300
5        300
6        100
7        100
8        300
8        200
8        100
8        101
8        102
9        100
9        200
9        300

My initial thoughts are to create columns with 1 and 0 say using case statement if course id 100 then 1 else 0 and try to sum to three new created columns by at employee level. any suggestion would be welcome.

CodePudding user response：

Use below simple approach

select Employee, array_length(array_agg(distinct course)) = 3 isCompliant
from your_table
where course in (100, 200, 300)
group by Employee

if applied to sample data in your question - output is

The better option is

select Employee, 
  ifnull(array_length(split(string_agg(distinct if(course in (100, 200, 300), '' || course, null)))), 0) = 3 isCompliant,
from your_table
group by Employee

with [what appears like better] output :o)