Write a query to find all students who scored above the class average in more than 3 subjects.

Answer

Compare each student's score against their class average per subject, count how many subjects they beat the average in, then keep those above 3.

Solution

WITH class_avg AS (
  SELECT subject, AVG(score) AS avg_score
  FROM Scores
  GROUP BY subject
)
SELECT s.student_id, COUNT(*) AS subjects_above_avg
FROM Scores s
JOIN class_avg c ON c.subject = s.subject
WHERE s.score > c.avg_score
GROUP BY s.student_id
HAVING COUNT(*) > 3;

Step by Step

1. The class_avg CTE computes the average score per subject across all students.
2. Each student's score row is joined against their subject's average.
3. WHERE s.score > c.avg_score keeps only the rows where that specific student beat the average in that specific subject.
4. Grouping by student and counting the remaining rows gives how many subjects each student scored above average in; HAVING COUNT(*) > 3 keeps only students who did so in more than 3 subjects.

Alternative: Window Function Instead of a Join

SELECT student_id, COUNT(*) AS subjects_above_avg
FROM (
  SELECT student_id, subject, score,
         AVG(score) OVER (PARTITION BY subject) AS avg_score
  FROM Scores
) x
WHERE score > avg_score
GROUP BY student_id
HAVING COUNT(*) > 3;

AVG(score) OVER (PARTITION BY subject) attaches the subject average to every row in a single pass, avoiding the explicit join - functionally identical, often preferred for readability once window functions are familiar.

Key Exam Traps

• "Above average" in more than 3 subjects requires the count to happen after filtering to only above-average rows - grouping first and counting all subjects, then filtering, would answer a different question entirely.
• Missing subject scores: If a student didn't take a particular subject, they simply have no row for it and aren't compared against that subject's average at all - not treated as a below-average score.
• HAVING COUNT(*) > 3 vs >= 3: "More than 3" is a strict inequality - double-check the exact wording against whichever operator is used, since "at least 3" would require >= instead.