Student Best Grade Course Selection

Medium

An academic analytics platform stores exam outcomes in an Enrollments table: student_id: unique identifier of the student course_id: unique identifier of the course grade: numeric grade earned by that student in that course Each (student_id, course_id) pair appears at most once. Task: For every student_id, return exactly one row representing that student's best course outcome. Choose the row with the highest grade. If multiple rows tie on the highest grade, choose the one with the smallest course_id. Return output with exactly these columns: student_id course_id grade Sort the result by student_id in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Enrollments:
| 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":1,"course_id":2,"grade":99},
  {"student_id":2,"course_id":2,"grade":95},
  {"student_id":3,"course_id":3,"grade":82}
]

Explanation

Student 2 has a tie at grade 95, so the smaller course_id (2) is chosen.

Example 2

Input

Enrollments:
| student_id | course_id | grade |
|------------|-----------|-------|
| 10         | 101       | 88    |
| 10         | 102       | 91    |
| 10         | 99        | 91    |
| 11         | 201       | 70    |

Output

[
  {"student_id":10,"course_id":99,"grade":91},
  {"student_id":11,"course_id":201,"grade":70}
]

Explanation

For student 10, grade 91 is highest and appears in two courses. Course 99 is selected because it is smaller than 102.

Example 3

Input

Enrollments:
| student_id | course_id | grade |
|------------|-----------|-------|
| 7          | 1         | 100   |
| 7          | 2         | 100   |
| 7          | 3         | 100   |
| 8          | 5         | 60    |
| 8          | 6         | 60    |

Output

[
  {"student_id":7,"course_id":1,"grade":100},
  {"student_id":8,"course_id":5,"grade":60}
]

Explanation

When all top grades are tied, the smallest course_id is selected for each student.

Constraints

1 <= number of rows in Enrollments <= 10,000
1 <= student_id <= 1,000,000,000
1 <= course_id <= 1,000,000,000
0 <= grade <= 100
(student_id, course_id) is unique
grade is never NULL
Return exactly one row per distinct student_id
Return columns exactly as: student_id, course_id, grade
Sort final result by student_id ascending
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: student_id, course_id, grade

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

Enrollments: | student_id | course_id | grade | |------------|-----------|-------| | 10 | 101 | 88 | | 10 | 102 | 91 | | 10 | 99 | 91 | | 11 | 201 | 70 |

Enrollments: | student_id | course_id | grade | |------------|-----------|-------| | 7 | 1 | 100 | | 7 | 2 | 100 | | 7 | 3 | 100 | | 8 | 5 | 60 | | 8 | 6 | 60 |