LeetCode 1112. Highest Grade For Each Student SQL Solution

Problem

LeetCode SQL Problem

1112. Highest Grade For Each Student

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

Solution - Use 2 CTEs

MySQL

Highest Grade For Each Student

WITH HighestGrade
AS (
	-- Get each student's highest grade
	SELECT E.student_id
		,max(E.grade) AS max_grade
	FROM Enrollments AS E
	GROUP BY E.student_id
	)
	,HighestGradeLowestCourseId
AS (
	-- In case of a grade tie, we want the course with the smallest course_id.
	SELECT E.student_id
		,min(E.course_id) AS min_course_id
	FROM Enrollments AS E
	INNER JOIN HighestGrade AS HG ON E.student_id = HG.student_id
		AND E.grade = HG.max_grade
	GROUP BY E.student_id
	)
-- For each student, output the highest grade with its corresponding course	
SELECT E.*
FROM Enrollments AS E
INNER JOIN HighestGradeLowestCourseId AS HGLC ON E.student_id = HGLC.student_id
	AND E.course_id = HGLC.min_course_id
-- The output must be sorted by increasing student_id.
ORDER BY E.student_id

TSQL

Highest Grade For Each Student

WITH HighestGrade
AS (
	-- Get each student's highest grade
	SELECT E.student_id
		,max(E.grade) AS max_grade
	FROM Enrollments AS E
	GROUP BY E.student_id
	)
	,HighestGradeLowestCourseId
AS (
	-- In case of a grade tie, we want the course with the smallest course_id.
	SELECT E.student_id
		,min(E.course_id) AS min_course_id
	FROM Enrollments AS E
	INNER JOIN HighestGrade AS HG ON E.student_id = HG.student_id
		AND E.grade = HG.max_grade
	GROUP BY E.student_id
	)
-- For each student, output the highest grade with its corresponding course	
SELECT E.*
FROM Enrollments AS E
INNER JOIN HighestGradeLowestCourseId AS HGLC ON E.student_id = HGLC.student_id
	AND E.course_id = HGLC.min_course_id
-- The output must be sorted by increasing student_id.
ORDER BY E.student_id

Solution - RANK() Window Function

MySQL

Highest Grade For Each Student

WITH StudGradeCourseIdRanked
AS (
	SELECT *
		-- The highest grade with its corresponding course for each student will have a rank value 1.
		-- In case of a tie, you should find the course with the smallest course_id.
		,rank() OVER (
			PARTITION BY student_id ORDER BY grade DESC
				,course_id ASC
			) AS rank_num
	FROM Enrollments
	)
SELECT student_id
	,course_id
	,grade
FROM StudGradeCourseIdRanked
WHERE rank_num = 1
-- The output must be sorted by increasing student_id.
ORDER BY student_id

TSQL

Highest Grade For Each Student

WITH StudGradeCourseIdRanked
AS (
	SELECT *
		-- The highest grade with its corresponding course for each student will have a rank value 1.
		-- In case of a tie, you should find the course with the smallest course_id.
		,rank() OVER (
			PARTITION BY student_id ORDER BY grade DESC
				,course_id ASC
			) AS rank_num
	FROM Enrollments
	)
SELECT student_id
	,course_id
	,grade
FROM StudGradeCourseIdRanked
WHERE rank_num = 1
-- The output must be sorted by increasing student_id.
ORDER BY student_id

Query Output

student_id	course_id	grade
1	2	99
2	2	95
3	3	82