The Credit-Weighted Average

A plain mean over a student's marks treats a two-credit elective as seriously as a four-credit core course, which the examiners at Fairhaven University consider indefensible for a transcript. The figure they want counts each mark once for every credit the course carries, so a good mark in a heavy course moves the number more than a good mark in a light one.

The rule the examiners set is deliberately unforgiving about outstanding results: the credits of a course a student is enrolled in count towards the figure whether or not a mark has come back for it, while a mark that has not come back contributes nothing on top. A student waiting on a result therefore scores low until it arrives, which is intended — the transcript should not flatter an incomplete record. A student with no sittings at all has no figure and is not listed.

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.

students — one row per enrolled student. gpa is the grade point average already on file for that student, on a 0-4 scale.

courses — one row per course the university runs. credits is how much the course counts towards a degree.

All three tables already exist in the database — there is nothing to create or load.

Task: Write a query that returns one row per student who has at least one sitting on record, with their name and their credit-weighted figure rounded to two decimal places as weighted_avg: the credit-weighted total of the marks on record, taken per credit the student is enrolled in — counting the credits of unmarked sittings in that per-credit measure but not their absent marks. Highest first; students sharing a figure are separated alphabetically by name.

Example output

Shape only — these students and figures are invented: