Rank Within Each Course

Feedback letters at Fairhaven University tell a student how they did relative to the people sitting the same course, not relative to the university. A 3.5 is a strong result in a harshly marked course and an ordinary one elsewhere, so a single university-wide ordering would tell most students something misleading about themselves.

Every sitting on record gets a place, including one still waiting on a mark — the letter has to say something about it rather than pretend it does not exist — and an unmarked sitting comes last within its course. Two sittings on the same mark share a place and the following one is skipped.

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.

The table already exists in the database — there is nothing to create or load.

Task: Write a query that returns one row per sitting, with student_id, course_id, grade and the sitting's standing inside its own course as course_rank — 1 for the best mark in that course, and the counting restarting at every course. Sittings sharing a mark share a standing and the following place is skipped; a sitting with no mark entered comes last in its course. Return the rows smallest course_id first, best standing first inside a course, and smallest student_id first if two are still level.

Example output

Shape only — these sittings are invented. Note the three things the shape is showing you: the counting restarts at every course, two sittings level on a mark share a standing and leave the next one unused, and an unmarked sitting still appears, with NULL in grade, at the bottom of its course: