How Do Our Busiest Students Perform?
An academic advisor at Fairhaven University suspects that students who take on a fuller timetable do better, not worse, and wants one figure to test the hunch against: the mean grade point average of the students carrying more than one course.
Deciding who those students are is the work. The student record says nothing about workload — a student's row has a name, a subject and an average, and no course count. The enrolment record is one row per student-course pair, so the count has to be worked out from how many rows a student owns there.
One of those rows is missing its grade. Ravi Shah is enrolled in a Spring 2024 course that has not been marked yet, and the enrolment is still an enrolment: he is carrying two courses, and leaving him out on the grounds that one has no grade would change the answer.
students — one row per student. gpa is their overall grade point average out of 4.0, already computed and stored.
| id | name | major | gpa |
|---|---|---|---|
| 1 | Nora Fischer | Computer Science | 3.8 |
| 2 | Omar Haddad | Statistics | 3.4 |
| 3 | Petra Novakova | Computer Science | 3.9 |
| 4 | Quinn Walsh | Mathematics | 3.1 |
| 5 | Ravi Shah | Statistics | 3.6 |
enrollments — one row per student-course pair. grade is the mark for that course, or NULL if it has not been awarded 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 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query returning exactly one row with a single column, avg_gpa: the mean of the stored gpa values of the students who are enrolled in more than one course, rounded to 4 decimal places. An enrolment with no grade recorded still counts as a course the student is carrying. The average is taken over the gpa column on the student row, not over any of the marks in the enrolment table. Strictly more than one: a student carrying exactly one course is not included.
Example output — shape only. If the students carrying several courses averaged 3.2857, the single row would read:
| avg_gpa |
|---|
| 3.2857 |
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