Students Whose SGPA Fell by More Than One Point
The student-mentoring cell calls in anyone whose SGPA fell sharply from one semester to the next.
- Difficulty: Medium
- Topics: Window Functions
- Dialect: MySQL
- Problem: #136
Problem statement
The student-mentoring cell calls in anyone whose SGPA fell sharply from one semester to the next. Some students have a gap in their record (a semester dropped for medical reasons), so each semester is compared with the student's previous recorded semester, whatever its number.
Return roll_no, semester, previous_sgpa, sgpa and sgpa_drop (previous_sgpa - sgpa, rounded to 2 decimal places) for every semester where the rounded drop is greater than 1.00. A drop of exactly 1.00 is not flagged, and a student's first recorded semester has nothing to compare with. Order the rows by roll_no, then by semester.
Tables
Table: SemesterResult
| Column | Type |
|---|---|
| roll_no | int |
| semester | int |
| sgpa | decimal |
Primary key: roll_no, semester.
semester runs from 1 to 8; sgpa is on a 10-point scale with two decimals.
Examples
Example 1
SemesterResult
| roll_no | semester | sgpa |
|---|---|---|
| 2101 | 1 | 8.42 |
| 2101 | 2 | 7.1 |
| 2101 | 3 | 7.35 |
| 2102 | 1 | 9.1 |
| 2102 | 2 | 8.1 |
| 2102 | 4 | 6.75 |
| 2103 | 1 | 6.2 |
| 2103 | 2 | 7.9 |
Output
| roll_no | semester | previous_sgpa | sgpa | sgpa_drop |
|---|---|---|---|---|
| 2101 | 2 | 8.42 | 7.1 | 1.32 |
| 2102 | 4 | 8.1 | 6.75 | 1.35 |
How to solve Students Whose SGPA Fell by More Than One Point
The question compares each row with the previous row of the same student, which is exactly what LAG does: partitioned by roll_no and ordered by semester, LAG(sgpa) returns the SGPA of the row before, and NULL for a student's first recorded semester. Because it works on row order rather than on semester - 1, a missing semester is skipped over naturally — semester 4 after semester 2 is compared with semester 2.
Window functions are evaluated after WHERE, so compute the previous SGPA in a CTE and filter outside it. The first semester's NULL makes the drop NULL, and NULL > 1 is not true, so it falls out with no extra condition.
The statement compares the drop rounded to two places. That matters: two-decimal grades stored as binary floating point can subtract to 1.0000000000000009, which a raw > 1 would wrongly flag. Without window functions, a self join to the row whose semester is the student's largest one below the current gives the same pairs at the cost of a correlated MAX per row.
Reference solution (MySQL)
WITH seq AS (
SELECT roll_no, semester, sgpa,
LAG(sgpa) OVER (PARTITION BY roll_no ORDER BY semester) AS previous_sgpa
FROM SemesterResult
)
SELECT roll_no, semester, previous_sgpa, sgpa, ROUND(previous_sgpa - sgpa, 2) AS sgpa_drop
FROM seq
WHERE ROUND(previous_sgpa - sgpa, 2) > 1
ORDER BY roll_no, semesterAnother way
SELECT roll_no, semester, previous_sgpa, sgpa, sgpa_drop FROM (SELECT r.roll_no, r.semester, p.sgpa AS previous_sgpa, r.sgpa, ROUND(p.sgpa - r.sgpa, 2) AS sgpa_drop FROM SemesterResult r JOIN SemesterResult p ON p.roll_no = r.roll_no AND p.semester = (SELECT MAX(q.semester) FROM SemesterResult q WHERE q.roll_no = r.roll_no AND q.semester < r.semester)) t WHERE sgpa_drop > 1 ORDER BY roll_no, semester← Course Completion Percentage for Each Learner · Placement Rate and Top Package by Branch →