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
| Column | Type |
|---|---|
| roll_no | int |
| name | varchar |
| branch | varchar |
| cgpa | decimal |
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_no | name | branch | cgpa |
|---|---|---|---|
| 2101 | Aarav | CSE | 8.4 |
| 2102 | Diya | CSE | 9.1 |
| 2103 | Ishaan | CSE | 6.2 |
| 2104 | Kavya | ECE | 7.5 |
| 2105 | Neha | ECE | 8.8 |
| 2106 | Rohan | ECE | 7.5 |
| 2107 | Sneha | ECE | 9.6 |
| 2108 | Vikram | MECH | 6.9 |
Output
| branch | students | median_cgpa |
|---|---|---|
| CSE | 3 | 8.4 |
| ECE | 4 | 8.15 |
| MECH | 1 | 6.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 branchAnother 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 branchAnother 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 →