Interview Question Bank · HAVING · RANK() over an aggregate
7. Instructors ranked by rating, minimum 5 reviews
MediumEducationQuestion #223
Concept: HAVING · RANK() over an aggregateshow
To rank groups by an average, GROUP BY the group, compute AVG, and put RANK() OVER (ORDER BY AVG(...) DESC) in the same SELECT, since window functions run after grouping. HAVING COUNT(*) >= n drops groups with too little data first, so a handful of perfect reviews can't top the list. Average the raw rows rather than per-course averages, or a course with two reviews weighs as much as one with six.
Example (different data):
SELECT c.name AS chef, COUNT(*) AS ratings, ROUND(AVG(r.stars), 2) AS avg_stars, RANK() OVER (ORDER BY AVG(r.stars) DESC) AS place FROM dish_ratings r JOIN chefs c ON c.id = r.chef_id GROUP BY c.id, c.name HAVING COUNT(*) >= 10;
Your task
Brightpath Academy gives a teaching award based on student course reviews. To be eligible, an instructor needs at least 5 reviews across all the courses they teach.
For instructors with at least 5 reviews, return instructor (name), reviews (count of reviews of their courses), avg_rating (average of those individual ratings, rounded to 2 decimals) and rating_rank (RANK by the unrounded average, highest first, ties sharing a rank). Sort by rating_rank, then instructor.
Starting Postgres in your browser…
Checking your access…