1112 Highest Grade for Each Student

Description

Table: Enrollments

• (student_id, course_id) is the primary key (combination of columns with unique values) of this table.
• grade is never NULL.

Problem Statement

Write a solution 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 result format is in the following example.

Example 1:

Input:

• Enrollments table:

Output:

Solution

In order to solve this problem, you need to find highest grade for each student. If a student has same grade for two different courses, you need to find the course with smallest course_id.

This can be done using RANK() window function. The window is partitioned by student_id and ordered by grade in descending order and course_id ascending.

1SELECT student_id, course_id, grade,
2    RANK() OVER (PARTITION BY student_id ORDER BY grade DESC, course_id) ranking
3    FROM Enrollments

Finally, you need to output only the rows with ranking = 1.

1WITH ranked_students AS (
2    SELECT student_id, course_id, grade,
3        RANK() OVER (PARTITION BY student_id ORDER BY grade DESC, course_id) ranking
4        FROM Enrollments
5) SELECT student_id, course_id, grade
6    FROM ranked_students
7    WHERE ranking = 1
8    ORDER BY student_id;