Drill 5 of 12 in Education questions
0

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…