Median CGPA of Each Branch

Hard
143.

Median CGPA of Each Branch

Recruiters visiting the campus ask for each branch's median CGPA rather than the average, which a handful of very low or very high grades can drag. The median is the middle CGPA when a branch's students are sorted; for an even number of students it is the average of the two middle values.

Return one row per branch with the columns branch, students and median_cgpa (rounded to 2 decimal places). Order the rows by branch.

Tables

Table: Student

ColumnType
roll_noint
namevarchar
branchvarchar
cgpadecimal

Primary key: roll_no.

One row per final-year student; cgpa is on a 10-point scale with one decimal and never NULL.

Example

The rows must come back in the order shown.

Student

roll_nonamebranchcgpa
2101AaravCSE8.4
2102DiyaCSE9.1
2103IshaanCSE6.2
2104KavyaECE7.5
2105NehaECE8.8
2106RohanECE7.5
2107SnehaECE9.6
2108VikramMECH6.9

Output

branchstudentsmedian_cgpa
CSE38.4
ECE48.15
MECH16.9
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.

Median CGPA of Each Branch — SQL Window Functions Problem

Recruiters visiting the campus ask for each branch's median CGPA rather than the average, which a handful of very low or very high grades can drag.

  • Difficulty: Hard
  • Topics: Window Functions, Aggregation
  • Dialect: MySQL
  • Problem: #143

Problem statement

Recruiters visiting the campus ask for each branch's median CGPA rather than the average, which a handful of very low or very high grades can drag. The median is the middle CGPA when a branch's students are sorted; for an even number of students it is the average of the two middle values.

Return one row per branch with the columns branch, students and median_cgpa (rounded to 2 decimal places). Order the rows by branch.

Tables

Table: Student

ColumnType
roll_noint
namevarchar
branchvarchar
cgpadecimal

Primary key: roll_no.

One row per final-year student; cgpa is on a 10-point scale with one decimal and never NULL.

Examples

Example 1

Student

roll_nonamebranchcgpa
2101AaravCSE8.4
2102DiyaCSE9.1
2103IshaanCSE6.2
2104KavyaECE7.5
2105NehaECE8.8
2106RohanECE7.5
2107SnehaECE9.6
2108VikramMECH6.9

Output

branchstudentsmedian_cgpa
CSE38.4
ECE48.15
MECH16.9

How to solve Median CGPA of Each Branch

MySQL has no MEDIAN aggregate, so build it from window functions. Within each branch, ROW_NUMBER() OVER (PARTITION BY branch ORDER BY cgpa, roll_no) gives every student a position 1…cnt in CGPA order, and COUNT(*) OVER (PARTITION BY branch) puts the branch size on every row. Ties in CGPA need a tie-breaker (roll_no) only so that the numbering is deterministic — tied students have the same CGPA, so which of them sits in the middle does not change the value.

For an odd size the median is at position (cnt + 1) / 2; for an even size it is the average of positions cnt / 2 and cnt / 2 + 1. Both cases are covered by keeping the rows whose position is FLOOR((cnt + 1) / 2) or FLOOR((cnt + 2) / 2) — one row for odd sizes, two for even — and taking AVG(cgpa) per branch. MAX(cnt) carries the size through the GROUP BY.

A neat alternative numbers the rows in both directions: the middle rows are those where the ascending and descending numbers differ by at most 1. Without windows, count for each student how many sort before them with a correlated subquery — quadratic, but it shows the definition plainly.

Reference solution (MySQL)

WITH ordered AS (
  SELECT branch, cgpa,
         ROW_NUMBER() OVER (PARTITION BY branch ORDER BY cgpa, roll_no) AS rn,
         COUNT(*) OVER (PARTITION BY branch) AS cnt
  FROM Student
)
SELECT branch, MAX(cnt) AS students, ROUND(AVG(cgpa), 2) AS median_cgpa
FROM ordered
WHERE rn IN (FLOOR((cnt + 1) / 2), FLOOR((cnt + 2) / 2))
GROUP BY branch
ORDER BY branch

Another way

SELECT branch, MAX(cnt) AS students, ROUND(AVG(cgpa), 2) AS median_cgpa FROM (SELECT branch, cgpa, COUNT(*) OVER (PARTITION BY branch) AS cnt, ROW_NUMBER() OVER (PARTITION BY branch ORDER BY cgpa, roll_no) AS up, ROW_NUMBER() OVER (PARTITION BY branch ORDER BY cgpa DESC, roll_no DESC) AS down FROM Student) t WHERE up <= down + 1 AND down <= up + 1 GROUP BY branch ORDER BY branch

Another way

SELECT s.branch, (SELECT COUNT(*) FROM Student z WHERE z.branch = s.branch) AS students, ROUND(AVG(s.cgpa), 2) AS median_cgpa FROM Student s WHERE (SELECT COUNT(*) FROM Student x WHERE x.branch = s.branch AND (x.cgpa < s.cgpa OR (x.cgpa = s.cgpa AND x.roll_no < s.roll_no))) * 2 BETWEEN (SELECT COUNT(*) FROM Student z WHERE z.branch = s.branch) - 2 AND (SELECT COUNT(*) FROM Student z WHERE z.branch = s.branch) GROUP BY s.branch ORDER BY s.branch

← Next-Month Retention of Learner Sign-up Cohorts · Course Funnel From Enrolment to Certificate →