Find Students Who Improved - Practice Coding Problems

Find Students Who Improved - Problem

Database Medium

You're working for an educational analytics company that tracks student performance over time. Your task is to identify students who have shown meaningful improvement in their academic journey.

The Challenge: Given a database table containing student exam scores across different subjects and dates, find all students who have demonstrated clear improvement in at least one subject.

What constitutes improvement?

The student must have taken at least 2 exams in the same subject on different dates
Their latest score in that subject must be higher than their first score

Input: A Scores table with columns:

student_id (int) - Unique identifier for each student
subject (varchar) - Subject name (e.g., "Math", "English")
score (int) - Score between 0-100
exam_date (varchar) - Date of the exam

Output: Return all students who improved, ordered by student_id and subject in ascending order.

Input & Output

basic_improvement.sql — Basic Case

$ Input: Scores table: | student_id | subject | score | exam_date | |------------|---------|-------|------------| | 1 | Math | 75 | 2023-01-15 | | 1 | Math | 82 | 2023-03-20 | | 2 | English | 90 | 2023-02-10 | | 2 | English | 85 | 2023-04-15 |

› Output: | student_id | subject | |------------|----------| | 1 | Math |

💡 Note: Student 1 improved in Math (75 → 82), while Student 2 declined in English (90 → 85). Only Student 1 shows improvement.

multiple_subjects.sql — Multiple Subjects

$ Input: Scores table: | student_id | subject | score | exam_date | |------------|---------|-------|------------| | 1 | Math | 70 | 2023-01-10 | | 1 | Math | 85 | 2023-02-15 | | 1 | English | 60 | 2023-01-12 | | 1 | English | 75 | 2023-02-18 |

› Output: | student_id | subject | |------------|----------| | 1 | English | | 1 | Math |

💡 Note: Student 1 improved in both subjects: Math (70 → 85) and English (60 → 75). Results are ordered by subject name.

single_exam.sql — Edge Case

$ Input: Scores table: | student_id | subject | score | exam_date | |------------|---------|-------|------------| | 1 | Math | 85 | 2023-01-15 | | 2 | English | 70 | 2023-02-10 | | 2 | English | 80 | 2023-03-15 |

› Output: | student_id | subject | |------------|----------| | 2 | English |

💡 Note: Student 1 has only one Math exam, so can't show improvement. Student 2 improved in English (70 → 80) with two exams.

Constraints

1 ≤ student_id ≤ 10⁵
1 ≤ score ≤ 100
Subject names are non-empty strings
Each exam_date is a valid date string
At least 2 exams in the same subject required for improvement consideration

Visualization

Tap to expand

Understanding the Visualization

Group by Student-Subject

Create separate progress charts for each student-subject combination

Time-Order Scores

Arrange scores chronologically to see the progression timeline

Identify First & Last

Mark the starting point and current position for each progress chart

Calculate Improvement

Compare endpoints to determine if there's upward trajectory

Key Takeaway

🎯 Key Insight: Window functions like FIRST_VALUE and LAST_VALUE allow us to compare temporal data points efficiently within each partition, making student progress analysis both elegant and performant.

Asked in

a Amazon 45 ⊞ Microsoft 38 G Google 32 f Meta 28

The optimal solution uses SQL window functions (FIRST_VALUE and LAST_VALUE) to efficiently identify students who improved. By partitioning data by student and subject, then ordering by exam date, we can capture first and last scores in a single pass through the data, making it much more efficient than multiple subqueries.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Subqueries)	O(n²)	O(n)	Use nested subqueries to find first and last scores for each student-subject pair
Window Functions (Optimal)	O(n log n)	O(n)	Use window functions to get first and last scores in a single efficient query

Brute Force (Multiple Subqueries) — Algorithm Steps

Find all unique student-subject combinations
For each combination, find the earliest exam date and corresponding score
For each combination, find the latest exam date and corresponding score
Compare first and last scores to check for improvement
Filter results where last score > first score

Visualization

Tap to expand

Step-by-Step Walkthrough

Find All Combinations

Identify all unique student-subject pairs

First Score Lookup

For each pair, find the earliest exam date and score

Last Score Lookup

For each pair, find the latest exam date and score

Compare Scores

Check if last score > first score for improvement

Code -

solution.c — C

// SQL Solution using Multiple Subqueries
#include <stdio.h>

int main() {
    const char* sqlQuery = 
    "SELECT DISTINCT \n"
    "    s1.student_id,\n"
    "    s1.subject\n"
    "FROM Scores s1\n"
    "WHERE (\n"
    "    SELECT score \n"
    "    FROM Scores s2 \n"
    "    WHERE s2.student_id = s1.student_id \n"
    "    AND s2.subject = s1.subject\n"
    "    ORDER BY s2.exam_date DESC \n"
    "    LIMIT 1\n"
    ") > (\n"
    "    SELECT score \n"
    "    FROM Scores s3 \n"
    "    WHERE s3.student_id = s1.student_id \n"
    "    AND s3.subject = s1.subject\n"
    "    ORDER BY s3.exam_date ASC \n"
    "    LIMIT 1\n"
    ")\n"
    "ORDER BY s1.student_id, s1.subject;";
    
    printf("%s\n", sqlQuery);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Multiple subqueries scan the table repeatedly for each student-subject combination

⚠ Quadratic Growth

Space Complexity

O(n)

Space for intermediate results and temporary tables

⚡ Linearithmic Space

28.5K Views

Medium Frequency

~18 min Avg. Time

1.3K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Multiple Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler