Students Scoring Above Their Section's Internal Average

Medium
134.

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. 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

ColumnType
roll_noint
namevarchar
sectionchar
marksint

Primary key: roll_no.

One row per student; marks is out of 50 and never NULL; section is A, B or C.

Example

The rows must come back in the order shown.

InternalMark

roll_nonamesectionmarks
2101AaravA42
2102DiyaA30
2103IshaanA36
2104KavyaB25
2105NehaB25
2106RohanB40
2107SnehaC33
2108VikramC33

Output

roll_nonamesectionmarkssection_avg
2101AaravA4236
2106RohanB4030
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
roll_noint
namevarchar
sectionchar
marksint

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_nonamesectionmarks
2101AaravA42
2102DiyaA30
2103IshaanA36
2104KavyaB25
2105NehaB25
2106RohanB40
2107SnehaC33
2108VikramC33

Output

roll_nonamesectionmarkssection_avg
2101AaravA4236
2106RohanB4030

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_no

Another 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_no

Another 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 →