Ranking Students Within Their Major

Fairhaven University's registrar prints an honour roll for each subject rather than one for the whole university, on the grounds that a grade point average means something different in Mathematics than it does in Computer Science. So the ordering has to begin again at each subject, and every student has to appear on their own subject's list — including Quinn Walsh, who is the entire Mathematics cohort and therefore top of it.

students — one row per enrolled student. major is the subject they are reading for, as text. gpa is their grade point average out of 4.0.

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

Task: Write a query returning three columns, name, major and gpa_rank, with one row for every student. gpa_rank is that student's position within their own major, 1 for the highest grade point average in that subject. Two students in the same major with exactly equal averages share a position and the position straight after it is skipped, so a subject could read 1, 2, 2, 4. Every student appears, including the sole student of a one-person major, who is a 1. Rows come back sorted by major, then by gpa_rank, then by name — three keys, because the first two together can still leave two students tied.

Example output — shape only, on an invented register; the two Economics students sharing position 2 show the shared number and the skip that follows it.