Highest Grade For Each Student - Practice Coding Problems

Highest Grade For Each Student - Problem

Database Medium

Imagine you're a database administrator for a university system that needs to generate academic performance reports. You have an Enrollments table that tracks every course a student has taken along with their grades.

Your task is to find the highest grade each student achieved across all their courses. However, there's a twist: if a student has the same highest grade in multiple courses, you should prioritize the course with the smallest course_id.

Database Schema:

Table: Enrollments
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| course_id     | int     |
| grade         | int     |
+---------------+---------+

Notes:

The combination of (student_id, course_id) is the primary key
grade is never NULL
Results should be ordered by student_id in ascending order

Goal: Write a SQL query that returns each student's highest grade along with the corresponding course. In case of ties, select the course with the smaller course_id.

Input & Output

basic_example.sql — Basic Case

$ Input: Enrollments table: | student_id | course_id | grade | |------------|-----------|-------| | 1 | 10 | 85 | | 1 | 11 | 95 | | 1 | 12 | 88 | | 2 | 13 | 80 | | 2 | 14 | 80 |

› Output: | student_id | course_id | grade | |------------|-----------|-------| | 1 | 11 | 95 | | 2 | 13 | 80 |

💡 Note: Student 1's highest grade is 95 in course 11. Student 2 has a tie with grade 80, so we pick the smaller course_id (13).

tie_breaking.sql — Tie Breaking Example

$ Input: Enrollments table: | student_id | course_id | grade | |------------|-----------|-------| | 1 | 20 | 90 | | 1 | 10 | 90 | | 1 | 30 | 85 | | 2 | 15 | 92 |

› Output: | student_id | course_id | grade | |------------|-----------|-------| | 1 | 10 | 90 | | 2 | 15 | 92 |

💡 Note: Student 1 has two courses with grade 90. We select course 10 (smaller course_id). Student 2 has one clear maximum.

single_course.sql — Edge Case

$ Input: Enrollments table: | student_id | course_id | grade | |------------|-----------|-------| | 1 | 100 | 75 | | 2 | 200 | 88 | | 3 | 300 | 92 |

› Output: | student_id | course_id | grade | |------------|-----------|-------| | 1 | 100 | 75 | | 2 | 200 | 88 | | 3 | 300 | 92 |

💡 Note: Each student has only one course, so each student's single grade is their highest grade.

Constraints

1 ≤ student_id, course_id ≤ 10⁴
0 ≤ grade ≤ 100
Each (student_id, course_id) combination appears at most once
grade is never NULL

Visualization

Tap to expand

Understanding the Visualization

Collect All Records

Gather all student enrollment records with their grades from different courses

Group by Student

Organize records by student using PARTITION BY student_id

Rank & Sort

Within each student's records, rank by grade (DESC) and course_id (ASC) for tie-breaking

Select Winners

Pick the top-ranked record (rn=1) for each student as their highest achievement

Key Takeaway

🎯 Key Insight: Window functions with proper ordering can solve complex ranking problems with tie-breaking in a single, efficient query. The ORDER BY clause in the window function does the heavy lifting: grade DESC finds the maximum, while course_id ASC breaks ties elegantly.

Asked in

G Google 45 a Amazon 38 f Meta 32 ⊞ Microsoft 28

The optimal solution uses a window function with ROW_NUMBER() to rank each student's grades in descending order, with course_id as a tie-breaker. This elegant approach handles both finding the maximum grade and resolving ties in a single pass, making it the most efficient solution with O(n log n) time complexity.

Common Approaches

Approach	Time	Space	Notes
✓ Subquery with MAX and MIN (Brute Force)	O(n²)	O(n)	Use nested subqueries to find max grade, then min course_id for ties
Window Function with ROW_NUMBER (Optimal)	O(n log n)	O(n)	Use window function to rank grades and handle ties in one pass
CTE with Aggregation (Two-Pass)	O(n log n)	O(n)	Use CTE to find max grades first, then join to get course details

Subquery with MAX and MIN (Brute Force) — Algorithm Steps

Find the maximum grade for each student using GROUP BY and MAX
For each student-max_grade pair, find the minimum course_id
Join back to get the final result with all required columns
Order the results by student_id

Visualization

Tap to expand

Step-by-Step Walkthrough

Find Max Grade

For each row, scan all records of that student to find maximum grade

Find Min Course

Among records with max grade, find minimum course_id

Filter Results

Keep only records matching both conditions

Code -

solution.c — C

// SQL Solution using subqueries
#include <stdio.h>

int main() {
    printf("%s\n", 
        "SELECT \n"
        "    e1.student_id,\n"
        "    e1.course_id,\n"
        "    e1.grade\n"
        "FROM Enrollments e1\n"
        "WHERE e1.grade = (\n"
        "    SELECT MAX(e2.grade) \n"
        "    FROM Enrollments e2 \n"
        "    WHERE e2.student_id = e1.student_id\n"
        ")\n"
        "AND e1.course_id = (\n"
        "    SELECT MIN(e3.course_id)\n"
        "    FROM Enrollments e3\n"
        "    WHERE e3.student_id = e1.student_id \n"
        "    AND e3.grade = (\n"
        "        SELECT MAX(e4.grade)\n"
        "        FROM Enrollments e4\n"
        "        WHERE e4.student_id = e1.student_id\n"
        "    )\n"
        ")\n"
        "ORDER BY e1.student_id;"
    );
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Multiple passes through the data for subqueries and joins

⚠ Quadratic Growth

Space Complexity

O(n)

Space for intermediate result sets and grouping operations

⚡ Linearithmic Space

52.2K Views

High Frequency

~15 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Subquery with MAX and MIN (Brute Force) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler