Course Champions
At the end of the year Fairhaven University hands out a small prize in each course for the best mark achieved in it. Before the certificates can be printed the awards office needs the winning mark for every course — the mark itself, not yet the name of the student who got it.
A course with no sittings on record has nobody to give a prize to and does not appear. Sittings still waiting on a mark cannot win anything either.
enrollments — one row per sitting: one student taking one course in one term. student_id points at a row in students and course_id at a row in courses. grade is the mark for that sitting, on the same 0-4 scale, and is NULL when no mark has been entered yet. semester is the term the sitting belongs to.
| 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 that returns one row per course that has at least one sitting on record, with course_id and the best mark achieved in that course as top_grade, smallest course_id first.
Example output
Shape only — these courses and marks are invented. A whole mark comes back plain, as 3 rather than 3.0:
| course_id | top_grade |
|---|---|
| 7 | 3.85 |
| 8 | 3 |
| 9 | 3.4 |
| 11 | 2.75 |
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