leetcode - 1112. Highest Grade For Each Student

난이도 : Medium

 

Table: Enrollments

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| course_id     | int     |
| grade         | int     |
+---------------+---------+
(student_id, course_id) is the primary key of this table.

 

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.

 

Example 1:

Input: 
Enrollments table:
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 2          | 2         | 95    |
| 2          | 3         | 95    |
| 1          | 1         | 90    |
| 1          | 2         | 99    |
| 3          | 1         | 80    |
| 3          | 2         | 75    |
| 3          | 3         | 82    |
+------------+-----------+-------+
Output: 
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 1          | 2         | 99    |
| 2          | 2         | 95    |
| 3          | 3         | 82    |
+------------+-----------+-------+

 

WITH sub AS (
            SELECT student_id
                 , course_id
                 , grade
                 , DENSE_RANK() OVER (PARTITION BY student_id ORDER BY grade DESC, course_id) rnk
            FROM Enrollments
            )
SELECT student_id
     , course_id
     , grade
FROM sub
WHERE rnk = 1

Accepted (9.73%)

 

 

SELECT student_id
     , MIN(course_id) course_id
     , grade
FROM Enrollments
WHERE (student_id, grade) IN (SELECT student_id
                                   , MAX(grade)
                              FROM Enrollments
                              GROUP BY student_id
                              )
GROUP BY student_id
ORDER BY student_id

Accepted (15.46%)