First, I'm assuming no knowledge so apologies if some of this is elementary.

The WITH statement at the start creates several CTEs that you can query further down in the code. This helps make the code a bit more readable.

A breakdown by section:

Students and ExamScores represents typical tables where your data would be stored.

StudentScores will query the Students table, parses their exams passed and creates a row for each exam passed.

Inside of UNNEST is the SPLIT(Students.ExamPassed,":") function which will split a delimited string into an array of strings. In this case, we pass the column ExamsPassed from the Students table and the string ":" as a delimiter.

You can read more about SPLIT here https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#split .