Above the Curve
"Above the curve" at Fairhaven University means beating the room you were actually in. A 3.5 in the harshest course on the timetable is a better result than a 3.6 in the most generous one, and the teaching committee wants the results that cleared their own course's bar — not the university's overall bar, which would just re-list the students in the easy courses.
Every course has its own bar, and each one has to be worked out from that course's own marks. A sitting with no mark entered has not cleared anything, and it does not count towards a course's bar either. Sitting exactly on the bar is not clearing it.
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 student_id, course_id and grade for every sitting whose mark is strictly above the mean mark of that same course. Smallest course_id first, and best mark first inside a course.
Example output
Shape only — these sittings and marks are invented:
| student_id | course_id | grade |
|---|---|---|
| 12 | 7 | 3.85 |
| 23 | 7 | 3.4 |
| 19 | 8 | 3.6 |
| 31 | 9 | 2.95 |
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