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;
    
    

All Problems

All Solutions