Whose Grades Are Strongest Overall?
The dean's list committee at Fairhaven University has one place left and two candidates being argued over. The chair wants the argument settled on marks actually earned this year rather than on the GPA figure carried in the student record, which is a year out of date and includes transfer credit.
That means going back to the enrollment register and working out, for each student, how they have done across their own courses — then taking the best of those. One student has an unmarked course, and an unmarked course is not a bad mark; it should not count either way.
enrollments — one row per student-course pairing. grade is the final mark on a 0-4 scale, or NULL if the course has not been marked yet.
| student_id | course_id | grade | semester |
|---|---|---|---|
| 1 | 1 | 3.7 | Fall2023 |
| 1 | 2 | 3.9 | Spring2024 |
| 2 | 1 | 3.2 | Fall2023 |
| 2 | 3 | 3.5 | Fall2023 |
| 3 | 2 | 4.0 | Spring2024 |
| 3 | 4 | 3.8 | Spring2024 |
| 4 | 3 | 2.9 | Fall2023 |
| 5 | 1 | 3.6 | Fall2023 |
| 5 | 2 | NULL | Spring2024 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query returning a single row with two columns, student_id and avg_grade, for the student with the highest average mark across their own enrollments, rounded to 2 decimal places. Unmarked enrollments contribute nothing to the average — they are neither a zero nor a reason to exclude the student. Exactly one row comes back. No two Fairhaven students tie on this data, so there is no tie to settle.
Example output — shape only, on an invented student and average.
| student_id | avg_grade |
|---|---|
| 7 | 3.42 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in