Welcome to Subscribe On Youtube
3421. Find Students Who Improved
Description
Table: Scores
+-++ \| student_id \| int \| \| subject \| varchar \| \| score \| int \| \| exam_date \| varchar \| +-+-+-++ \| student_id \| subject \| score \| exam_date \| ++-+-++
Output:
++-+-+--+ \| 101 \| Math \| 70 \| 85 \| \| 102 \| Math \| 80 \| 85 \| \| 104 \| Physics \| 75 \| 85 \| ++-----+
Explanation:
- Student 101 in Math: Improved from 70 to 85
- Student 101 in Physics: No improvement (dropped from 65 to 60)
- Student 102 in Math: Improved from 80 to 85
- Student 103 in Math: Only one exam, not eligible
- Student 104 in Physics: Improved from 75 to 85
Result table is ordered by student_id, subject.
</div>
Solutions
Solution 1: Window Function + Subquery + Conditional Filtering
First, we use the window function ROW_NUMBER()
to calculate the ranking of each student’s exam date in each subject, separately calculating the first and most recent exam rankings for each student in each subject.
Then, we use a subquery JOIN
operation to join the scores of the first and most recent exams together. Finally, we filter out the students whose most recent exam scores are higher than their first exam scores according to the problem requirements.
-
WITH RankedScores AS ( SELECT student_id, subject, score, exam_date, ROW_NUMBER() OVER ( PARTITION BY student_id, subject ORDER BY exam_date ASC ) AS rn_first, ROW_NUMBER() OVER ( PARTITION BY student_id, subject ORDER BY exam_date DESC ) AS rn_latest FROM Scores ), FirstAndLatestScores AS ( SELECT f.student_id, f.subject, f.score AS first_score, l.score AS latest_score FROM RankedScores f JOIN RankedScores l ON f.student_id = l.student_id AND f.subject = l.subject WHERE f.rn_first = 1 AND l.rn_latest = 1 ) SELECT * FROM FirstAndLatestScores WHERE latest_score > first_score ORDER BY 1, 2;