Interview Question Bank · AVG() OVER (PARTITION BY) · filter in an outer query
5. Students who beat their class average
MediumEducationQuestion #221
Concept: AVG() OVER (PARTITION BY) · filter in an outer queryshow
A window function like AVG(score) OVER (PARTITION BY class_id) puts each group's average on every row without collapsing the rows, so a student's score and their class average sit side by side. Window results don't exist yet when WHERE runs, so compute them in a CTE or subquery and filter in the outer query. AVG skips NULLs, so ungraded rows don't drag the average down.
Example (different data):
WITH t AS (
SELECT store, clerk, sales,
AVG(sales) OVER (PARTITION BY store) AS store_avg
FROM weekly_sales
)
SELECT store, clerk, sales
FROM t
WHERE sales > store_avg;Your task
Brightpath Academy wants to send a 'top of the class' note to every student who beat their course's average final score.
Among graded enrollments (final_score not NULL), return course (title), student (name), final_score, course_avg (the course's average final score, rounded to 1 decimal) and above_by (final_score minus the unrounded average, rounded to 1 decimal) for every student who scored above their course's average. Sort by course, then above_by descending, then student.
Starting Postgres in your browser…
Checking your access…