Highest Grade For Each Student - Solution & Explanation

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.