Skip to main content
Talentd

Highest Grade For Each Student - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min readAsked at: Google, Coursera
Practice this problem

Problem Statement

Table: Enrollments

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| course_id     | int     |
| grade         | int     |
+---------------+---------+
(student_id, course_id) is the primary key (combination of columns with unique values) of this table.
grade is never NULL.

 

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:
+------------+-------------------+
| 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    |
+------------+-----------+-------+

Approach Overview

Problem Overview: You are given an Enrollments table containing student_id, course_id, and grade. For each student, return the course where they achieved their highest grade. If multiple courses share the same highest grade, choose the course with the smallest course_id. The result should contain one row per student.

Approach 1: RANK() Window Function (O(n log n) time, O(n) space)

This approach uses a SQL window function to rank courses for each student. Partition rows by student_id and order them by grade DESC so higher grades appear first. When two courses have the same grade, order by course_id ASC to prioritize the smaller course ID. Using RANK() OVER(PARTITION BY student_id ORDER BY grade DESC, course_id ASC) assigns rank 1 to the best course for each student. After ranking, filter rows where rank = 1. The database internally sorts rows within each partition, which leads to roughly O(n log n) time complexity and O(n) space for ranking metadata.

Window functions are ideal when you need ordered comparisons within groups. They keep the query readable and avoid multiple nested aggregations. If your database supports window functions (MySQL 8+, PostgreSQL, SQL Server), this is usually the cleanest solution. Explore more patterns in SQL and window functions.

Approach 2: Subquery with Aggregation (O(n) time, O(n) space)

This method first computes the maximum grade per student using a grouped subquery: SELECT student_id, MAX(grade). That result identifies each student's best grade but does not resolve ties between multiple courses. Join or filter the original table against this subquery so only rows with the maximum grade remain. If multiple courses share that grade, select the smallest course_id using MIN(course_id) while grouping by student_id and grade. The database performs a single aggregation pass and a grouping step, giving roughly O(n) processing with O(n) intermediate storage depending on execution strategy.

This approach works in older SQL environments that lack window functions. It relies on classic aggregation patterns common in database interview questions.

Recommended for interviews: The window function solution is the most expressive and matches how modern SQL queries are written in production. Interviewers still appreciate seeing the aggregation + subquery approach because it shows you understand grouping logic and tie-breaking rules. Demonstrating both solutions shows depth: the subquery proves fundamental SQL knowledge, while the window function shows practical mastery of advanced query tools.

Approach 1: RANK() OVER() Window Function

We can use the RANK() OVER() window function to sort the grades of each student in descending order. If the grades are the same, we sort them in ascending order by course number, and then select the record with a rank of 1 for each student.

Code

MySQL

MySQL

Try this approach in the editor →

Approach 2: Subquery

We can first query the highest grade of each student, and then query the minimum course number corresponding to the highest grade of each student.

Code

MySQL

MySQL

Try this approach in the editor →

Complexity Comparison

ApproachComplexity
RANK() OVER() Window Function—
Subquery—

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
RANK() Window FunctionO(n log n)O(n)Best readability when window functions are supported (MySQL 8+, PostgreSQL)
Subquery with MAX + MINO(n)O(n)When window functions are unavailable or when using classic SQL aggregation patterns

Video Solution

Advanced SQL: WINDOW Function Explained - Difference b/w ROW_NUMBER, RANK & DENSE_RANK Leetcode 1112 • Everyday Data Science • 4,542 views views

Watch 1 more video solutions →

Frequently Asked Questions

Is Highest Grade For Each Student easy or hard?
The problem is rated Medium because it combines grouping logic with a tie-breaking rule. Basic SQL users can compute the maximum grade, but correctly handling ties and returning the smallest course_id requires stronger understanding of window functions or multi-step aggregation.
Highest Grade For Each Student Python/Java solution
Because this is a database problem, the solution is written in SQL rather than Python or Java. In coding interviews or backend systems, the query would run inside application code written in Python, Java, or another language using a database driver.
How to solve Highest Grade For Each Student in O(n)?
Use a grouped subquery to compute each student's maximum grade: SELECT student_id, MAX(grade). Then filter the original table to keep rows matching that grade and select the smallest course_id with MIN(course_id). This avoids per-partition sorting and behaves roughly like O(n) aggregation in many database engines.
What is the best approach for Highest Grade For Each Student?
The most practical solution uses the RANK() window function. Partition rows by student_id and order by grade descending and course_id ascending. Selecting rows with rank = 1 returns the highest grade per student while automatically resolving ties by choosing the smallest course_id. This approach runs in about O(n log n) time due to sorting within partitions.
Is Highest Grade For Each Student asked at Google/Amazon/Meta?
SQL aggregation and window function problems like this frequently appear in database interviews at companies such as Amazon, Meta, and Google. Variations often ask for the top record per group, highest salary per department, or best score per user.
What data structure is used in Highest Grade For Each Student?
This problem relies on relational database operations rather than traditional data structures. The key concepts are SQL grouping, aggregation functions like MAX(), and window functions such as RANK() or ROW_NUMBER(). Internally the database may use sorting or hash-based grouping.
What is the time complexity of Highest Grade For Each Student?
The window function solution typically runs in O(n log n) time because the database sorts rows within each student partition. The subquery aggregation approach is closer to O(n) since it performs a grouping operation to compute MAX(grade) and then filters rows. Actual performance depends on indexes and the database query planner.

Ready to solve this problem?

Practice Highest Grade For Each Student with our built-in code editor and test cases.

Practice on FleetCode
Previous

Department Highest Salary