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)