Suppose your team interviews undergraduate candidates across many different colleges. You are looking to check which candidates scored the highest from each college.

Given the below tables, write a SQL query (using a window function) to show which candidates scored the highest from each college.

Table: candidateColleges...