Interview Question Bank · RANK() · PARTITION BY · NULLs sort first
6. Top student in each course, ties included
MediumEducationQuestion #222
Concept: RANK() · PARTITION BY · NULLs sort firstshow
To find the best row in each group, rank inside each group with RANK() OVER (PARTITION BY group ORDER BY value DESC) and keep rank 1. RANK gives tied rows the same number, so a shared first place returns every winner, while ROW_NUMBER picks one arbitrarily. In Postgres, DESC puts NULLs first, so filter them out before ranking, or an ungraded row will 'win'.
Example (different data):
WITH r AS (
SELECT league, team, points,
RANK() OVER (PARTITION BY league ORDER BY points DESC) AS rnk
FROM standings
WHERE points IS NOT NULL
)
SELECT league, team, points FROM r WHERE rnk = 1;Your task
Brightpath Academy puts the highest final score in each course on its honor roll. When students share the top score, all of them are listed.
For each course with at least one graded enrollment, return course (title), student (name) and final_score for the student(s) with the highest final_score, keeping ties. Sort by course, then student.
Starting Postgres in your browser…
Checking your access…