Students Scoring Above Their Section's Internal Average
After the first internal assessment, each class teacher wants to know which students in their section scored above the section's average.
- Difficulty: Medium
- Topics: Subqueries, Window Functions
- Dialect: MySQL
- Problem: #134
Problem statement
After the first internal assessment, each class teacher wants to know which students in their section scored above the section's average. Each section is compared only with itself.
Return roll_no, name, section, marks and section_avg (the average marks of the student's section, rounded to 2 decimal places) for every student whose marks are strictly greater than their section's average. Order the rows by section, then by marks highest first, then by roll_no.
Tables
Table: InternalMark
| Column | Type |
|---|---|
| roll_no | int |
| name | varchar |
| section | char |
| marks | int |
Primary key: roll_no.
One row per student; marks is out of 50 and never NULL; section is A, B or C.
Examples
Example 1
InternalMark
| roll_no | name | section | marks |
|---|---|---|---|
| 2101 | Aarav | A | 42 |
| 2102 | Diya | A | 30 |
| 2103 | Ishaan | A | 36 |
| 2104 | Kavya | B | 25 |
| 2105 | Neha | B | 25 |
| 2106 | Rohan | B | 40 |
| 2107 | Sneha | C | 33 |
| 2108 | Vikram | C | 33 |
Output
| roll_no | name | section | marks | section_avg |
|---|---|---|---|---|
| 2101 | Aarav | A | 42 | 36 |
| 2106 | Rohan | B | 40 | 30 |
How to solve Students Scoring Above Their Section's Internal Average
The comparison is between a row and an aggregate over its own group. A correlated subquery expresses it directly: for each outer row m, (SELECT AVG(x.marks) FROM InternalMark x WHERE x.section = m.section) is the average of that student's section, and the WHERE clause keeps the student when their marks are strictly greater.
Two other forms compute each section's average only once. A derived table grouped by section, joined back on section, gives every student their section average as a column. A window AVG(marks) OVER (PARTITION BY section) does the same without a join, though the filter must then go in an outer query because WHERE cannot see window results.
Compare against the exact average and round only the displayed section_avg: in a section averaging 33.33, a student with 33 is below it even though the rounded figure looks close. Students level with the average, including whole sections of equal marks, are excluded by the strict comparison.
Reference solution (MySQL)
SELECT m.roll_no, m.name, m.section, m.marks,
ROUND((SELECT AVG(x.marks) FROM InternalMark x WHERE x.section = m.section), 2) AS section_avg
FROM InternalMark m
WHERE m.marks > (SELECT AVG(x.marks) FROM InternalMark x WHERE x.section = m.section)
ORDER BY m.section, m.marks DESC, m.roll_noAnother way
SELECT roll_no, name, section, marks, ROUND(avg_marks, 2) AS section_avg FROM (SELECT roll_no, name, section, marks, AVG(marks) OVER (PARTITION BY section) AS avg_marks FROM InternalMark) t WHERE marks > avg_marks ORDER BY section, marks DESC, roll_noAnother way
SELECT m.roll_no, m.name, m.section, m.marks, ROUND(s.avg_marks, 2) AS section_avg FROM InternalMark m JOIN (SELECT section, AVG(marks) AS avg_marks FROM InternalMark GROUP BY section) s ON s.section = m.section WHERE m.marks > s.avg_marks ORDER BY 3, 4 DESC, 1← Courses Where More Than a Quarter of Students Failed · Course Completion Percentage for Each Learner →