Find Students Who Improved - Practice Coding Problems

Find Students Who Improved - Problem

Database Medium

You are given a table Scores that contains information about students' exam scores in different subjects over multiple dates.

Table Schema:

Column Name	Type
student_id	int
subject	varchar
score	int
exam_date	varchar

Primary Key: (student_id, subject, exam_date)

Each row contains information about a student's score in a specific subject on a particular exam date. The score is between 0 and 100 (inclusive).

Task: Write a SQL query to find students who have shown improvement. A student is considered to have shown improvement if they meet both 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.

Table Schema

Scores

Column Name	Type	Description
`student_id` PK	int	Unique identifier for each student
`subject` PK	varchar	Subject name (e.g., Math, Science)
`score`	int	Exam score between 0 and 100
`exam_date` PK	varchar	Date when the exam was taken

Primary Key: (student_id, subject, exam_date)

Note: Each row represents a unique exam attempt by a student in a subject on a specific date

Input & Output

Example 1 — Students with Mixed Performance

Input Table:

student_id	subject	score	exam_date
1	Math	70	2023-01-15
1	Math	85	2023-03-20
1	Physics	90	2023-01-10
2	Math	80	2023-01-12
2	Math	75	2023-02-15
3	Physics	65	2023-01-08

Output:

student_id	subject
1	Math

💡 Note:

Student 1 improved in Math (70 → 85) and took multiple exams. Student 1 only took Physics once, so no comparison possible. Student 2 declined in Math (80 → 75). Student 3 only took Physics once.

Example 2 — Multiple Subjects with Improvement

Input Table:

student_id	subject	score	exam_date
1	Math	60	2023-01-01
1	Math	80	2023-02-01
1	Science	70	2023-01-05
1	Science	90	2023-02-05
2	Math	85	2023-01-10
2	Math	95	2023-03-10

Output:

student_id	subject
1	Math
1	Science
2	Math

💡 Note:

Student 1 improved in both Math (60 → 80) and Science (70 → 90). Student 2 improved in Math (85 → 95). All results ordered by student_id, subject.

Constraints

1 ≤ student_id ≤ 1000
1 ≤ score ≤ 100
subject contains only letters and spaces
exam_date is in valid date format

Visualization

Tap to expand

Understanding the Visualization

Input Analysis

Multiple exam records per student-subject

Window Function

Compare first and last scores

Filter Results

Only improved students with 2+ exams

Key Takeaway

🎯 Key Insight: Use window functions to efficiently compare temporal data within groups

Asked in

A Amazon 23 M Microsoft 18 G Google 15

Use window functions with FIRST_VALUE and LAST_VALUE to compare initial and final scores for each student-subject combination. Filter for students with multiple exams where the last score exceeds the first.

Table Schema

Scores

Column Name	Type	Description
`student_id` PK	int	Unique identifier for each student
`subject` PK	varchar	Subject name (e.g., Math, Science)
`score`	int	Exam score between 0 and 100
`exam_date` PK	varchar	Date when the exam was taken

Primary Key: (student_id, subject, exam_date)

Note: Each row represents a unique exam attempt by a student in a subject on a specific date

Common Approaches

Approach	Time	Space	Notes
✓ Window Functions with First/Last Values	O(n log n)	O(n)	Use window functions to find first and last scores for each student-subject combination

Window Functions with First/Last Values — Algorithm Steps

Step 1: Partition by student_id and subject, order by exam_date
Step 2: Use FIRST_VALUE and LAST_VALUE to get initial and final scores
Step 3: Filter for students with multiple exams and improvement
Step 4: Select distinct student-subject pairs and order results

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition

Group by student_id and subject

Order

Sort by exam_date within each partition

Compare

Check if last_score > first_score

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting required for window function ordering by exam_date

⚡ Linearithmic

Space Complexity

O(n)

Window frame storage for partitioned data

⚡ Linearithmic Space

28.5K Views

Medium Frequency

~18 min Avg. Time

890 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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Window Functions with First/Last Values — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler