Students Whose SGPA Fell by More Than One Point

Medium
136.

Students Whose SGPA Fell by More Than One Point

MediumWindow Functions

MySQL

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

ColumnType
roll_noint
semesterint
sgpadecimal

Primary key (together): roll_no, semester.

semester runs from 1 to 8; sgpa is on a 10-point scale with two decimals.

Example

The rows must come back in the order shown.

SemesterResult

roll_nosemestersgpa
210118.42
210127.1
210137.35
210219.1
210228.1
210246.75
210316.2
210327.9

Output

roll_nosemesterprevious_sgpasgpasgpa_drop
210128.427.11.32
210248.16.751.35
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 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

ColumnType
roll_noint
semesterint
sgpadecimal

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_nosemestersgpa
210118.42
210127.1
210137.35
210219.1
210228.1
210246.75
210316.2
210327.9

Output

roll_nosemesterprevious_sgpasgpasgpa_drop
210128.427.11.32
210248.16.751.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, semester

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