Top candidates by college
Question
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
| college_id | candidate_name |
|---|---|
| 123456 | john_smith |
| 123456 | sarah_daniels |
| 123457 | tim_cook |
| 123457 | lisa_perelli |
| 123457 | jenny_west |
| 123457 | karl_tran |
| 123457 | tammy_turner |
Table: candidateInterviews
Each row represents a unique interview/candidate as well as the candidate's score (an integer from 1-5, with 5 representing the highest possible score).
| interview_id | candidate_name | interview_score |
|---|---|---|
| 12 | john_smith | 4 |
| 22 | sarah_daniels | 3 |
| 23 | tim_cook | 3 |
| 25 | lisa_perelli | 5 |
| 26 | jenny_west | 2 |
| 27 | karl_tran | 2 |
| 28 | tammy_turner | 4 |