Find Students Who Improved solution and explanation
4 more videos on this problem
Leetcode MEDIUM 3421 - SELF JOINs in SQL - Find Students Who Improved | Everyday Data ScienceEveryday Data Science · 10:47 · 1K views
3421. Find Students Who Improved – SQL Tutorial (LeetCode) #insightvanta #leetcode #sql #3421InsightVanta · 11:07 · 237 views
LeetCode 3421 – Find Students Who Improved | Pandas| Leetcode Interview QuestionThe Knowledge Academy · 2:35 · 85 views
SQL vs Pandas | Mastering Window Functions & Grouping: LeetCode 3421 SolutionDataHead Girl · 11:38 · 63 views
Leetcode 3421 Find Students Who Improved - Medium Complexity SQL Question ExplainedAlgorithmicIQ · 9:37 · 49 views
The problem
Table: Scores
+-------------+---------+ | Column Name | Type | +-------------+---------+ | student_id | int | | subject | varchar | | score | int | | exam_date | varchar | +-------------+---------+ (student_id, subject, exam_date) is the primary key for this table. Each row contains information about a student's score in a specific subject on a particular exam date. score is between 0 and 100 (inclusive).
Write a solution to find the students who have shown improvement. A student is considered to have shown improvement if they meet both of these conditions:
- Have taken exams in the same subject on at least two different dates
- Their latest score in that subject is higher than their first score
Return the result table ordered by student_id, subject in ascending order.
The result format is in the following example.
Example:
Input:
Scores table:
+------------+----------+-------+------------+ | student_id | subject | score | exam_date | +------------+----------+-------+------------+ | 101 | Math | 70 | 2023-01-15 | | 101 | Math | 85 | 2023-02-15 | | 101 | Physics | 65 | 2023-01-15 | | 101 | Physics | 60 | 2023-02-15 | | 102 | Math | 80 | 2023-01-15 | | 102 | Math | 85 | 2023-02-15 | | 103 | Math | 90 | 2023-01-15 | | 104 | Physics | 75 | 2023-01-15 | | 104 | Physics | 85 | 2023-02-15 | +------------+----------+-------+------------+
Output:
+------------+----------+-------------+--------------+ | student_id | subject | first_score | latest_score | +------------+----------+-------------+--------------+ | 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.
Approach overview
Problem Overview: You get a Scores table with student_id, subject, score, and exam_date. Return students who took at least two exams in the same subject and whose latest score in that subject is higher than their first score. Output student_id, subject, first_score, and latest_score, ordered by student_id and subject.
Approach 1: Self-Join on Exam Dates (O(n^2) time, O(1) extra space)
Join the Scores table to itself on student_id and subject, then use correlated subqueries to find the minimum and maximum exam_date for each group. For each candidate row, you scan the table again to fetch the matching first and latest scores. Every lookup is a full scan, so the worst case is quadratic in the number of rows. Use this only to build intuition. It breaks down quickly on large tables.
Approach 2: Window Functions with ROW_NUMBER and Conditional Filtering (O(n log n) time, O(n) space)
Partition rows by student_id and subject, and order each partition by exam_date. Assign ROW_NUMBER() ascending to tag the first exam and ROW_NUMBER() descending to tag the latest exam. Add COUNT(*) OVER (PARTITION BY student_id, subject) to check that the student took at least two exams. Wrap this in a CTE, then self-join the rows tagged as first and last. Filter on exam_count >= 2 and latest score greater than first score. The sort inside each partition is the dominant cost, which gives O(n log n). The rest runs in a single pass over the sorted partitions, with no correlated lookups. This is the approach to use in MySQL 8+.
A close variant uses FIRST_VALUE(score) and LAST_VALUE(score) with a frame of ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Then apply SELECT DISTINCT and the same filters. It has the same complexity and fewer joins.
WITH ranked 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_last, COUNT(*) OVER (PARTITION BY student_id, subject) AS exam_count FROM Scores) SELECT f.student_id, f.subject, f.score AS first_score, l.score AS latest_score FROM ranked f JOIN ranked l ON f.student_id = l.student_id AND f.subject = l.subject AND f.rn_first = 1 AND l.rn_last = 1 WHERE f.exam_count >= 2 AND l.score > f.score ORDER BY f.student_id, f.subject;Recommended for interviews: Interviewers expect the window function approach. The self-join shows you understand the problem and can express the first and latest comparison. The window function solution shows you can reduce it to one ordered pass per group and filter cleanly. Mention the O(n log n) cost of the sort and explain why the correlated subquery version does not scale. Related topics: database, window functions, and subqueries.
Solution
Thinking
We need students whose latest score in a subject exceeds their first score. Aggregating
MIN/MAX(exam_date)and joining back can attach the wrong score to a date.Window functions mark both the earliest and the latest exam in one pass.
We compute
ROW_NUMBER()partitioned by(student_id,subject)in both date orders, self-join the two ranks, keep rows withlatest_score>first_score, and sort as required.
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;Complexity analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Self-Join with Correlated Subqueries | O(n^2) | O(1) extra | Small tables or building intuition |
| Window Functions with ROW_NUMBER and Conditional Filtering | O(n log n) | O(n) | MySQL 8+, general case and interviews |
| FIRST_VALUE and LAST_VALUE with DISTINCT | O(n log n) | O(n) | When you want fewer joins and a shorter query |
Frequently asked questions
Find Students Who Improved Python solution?
In this SQL problem, the solution is written in SQL, not Python. Use the window function query in MySQL, which partitions by student_id and subject, orders by exam_date, and filters for students whose latest score is greater than their first score across at least two exams.
Is Find Students Who Improved easy or hard?
It is a medium-difficulty SQL problem with an acceptance rate of about 50 percent. The hard part is correctly identifying the first and latest exam per student and subject. Once you use ROW_NUMBER() in both directions, the filtering is straightforward.
How to solve Find Students Who Improved in O(n log n)?
Use ROW_NUMBER() with PARTITION BY student_id, subject and ORDER BY exam_date in both ascending and descending order. Add COUNT(*) OVER the same partition to enforce at least two exams. Join the first-row and last-row results and keep rows where the latest score is greater than the first score.
What is the best approach for Find Students Who Improved?
The best approach uses window functions in MySQL 8+. Partition the rows by student_id and subject, order by exam_date, and tag the first and latest exams with ROW_NUMBER(). Filter for groups with at least two exams and a latest score higher than the first.
What data structure is used in Find Students Who Improved?
The problem uses a relational table, Scores, as the input. The solution relies on window functions that partition and order rows, along with a CTE or subquery to hold the intermediate ranked result. No explicit hash map or tree is needed.
What is the time complexity of Find Students Who Improved?
The optimal window function solution runs in O(n log n) time because of the sort within each student and subject partition. It uses O(n) space for the ranked intermediate result. The naive self-join with correlated subqueries runs in O(n^2) time.
Is Find Students Who Improved asked at Google, Amazon, or Meta?
This problem is tagged Database and appears on LeetCode as a medium SQL question. Companies that run SQL screens, including Amazon, Meta, and Google, use problems of this type to test window functions. Check the company-specific tags on LeetCode for the most current data.