SQL Interview Questions – Highest Grade For Each Students

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 course_id.

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

Solution –

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.