Problem Description –
Write a SQL query to find the highest grade with its corresponding course for each student. In case of a tie, you should find the course with the smallest
Return the result table ordered by
student_id in ascending order.
The query result format is in the following example.
Difficulty Level – Medium
Problem Link – Highest Grade
SELECT student_id, course_id, grade FROM ( SELECT student_id, course_id, grade, DENSE_RANK() OVER(PARTITION BY student_id ORDER BY grade DESC, course_id) as rnk FROM enrollments ) x WHERE rnk=1 ORDER BY 1
For ranking we are using the DENSE_RANK function. And in the problem it is given that we need to find the highest grade with its corresponding course for each student, so we first partition by student_id then order the grade in descending order. In the question it is also given that in case of tie we need to find the course with the smallest
course_i, so we also added a second order by condition by course_id. Then in the outer query we only selected the rows where rank is 1 to get the highest grades.