Top candidates by college


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


Access restricted

Subscribe to premium account to see the solution.

Get premium now