How Are Students Doing in Each Course?
An instructor at Fairhaven University suspects she is marking harder than her colleagues, and wants the evidence before raising it at the faculty meeting. What she needs is the typical mark in every course being offered, so hers can be read next to the rest.
One wrinkle sits in the data. A student can be enrolled before their work is marked, and that shows up as an empty grade. An empty grade is not a zero — treating it as one would drag a course's figure down and invent a problem that is not there.
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 two columns, course_id and avg_grade, with one row for every course that has at least one enrollment. avg_grade is the average of the marks actually recorded, rounded to 2 decimal places; an unmarked enrollment contributes nothing at all and must not be counted as a zero. Rows come back in ascending course_id order.
Example output — shape only; the figures below are invented, not this data's answer.
| course_id | avg_grade |
|---|---|
| 5 | 2.85 |
| 6 | 3.4 |
| 8 | 2.6 |
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