Iterating over a column to see check certain conditions in Bigquery

Question

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. Problem statement. Say employee who has completed course 100,200,300 They are compliant in Bakery else they are non-complaint. Employee must complete all three courses to be compliant.

Employee who has completed course 100 and 300 They are compliant in Fruit & Veg else they are non-complaint. They must complete above mention two courses to be compliant.

Employee who has completed course 75,85,95 They are compliant in Grocery else non-compliant. Same here they must have done three courses so that they can be compliant.

Employee who has completed 101 and 102 They are compliant in Nighfill else non-compliant.

Please note employee must complete all the sets to be compliant in relevant categories

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

Structure of Data

I am still thinking about the solution.

Answer 1

Consider below approach

select Employee, 
  ifnull(array_length(split(string_agg(distinct if(course in (100,200,300), '' || course, null)))), 0) = 3 isBakeyCompliant,
  ifnull(array_length(split(string_agg(distinct if(course in (100,300) and not course in (200), '' || course, null)))), 0) = 2 isFruitVegCompliant,
  ifnull(array_length(split(string_agg(distinct if(course in (75,85,95), '' || course, null)))), 0) = 3 isGroceryCompliant,
  ifnull(array_length(split(string_agg(distinct if(course in (101,102), '' || course, null)))), 0) = 3 isNighfillCompliant,
from your_table
group by Employee    

with output