Find Students Who Improved - Problem

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

Table Schema:

Column NameType
student_idint
subjectvarchar
scoreint
exam_datevarchar

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
Find Students Who Improved INPUT: Exam Scores ID Name Subject Date Score 1 Alice Math Jan-01 70 1 Alice Math Feb-15 85 2 Bob Math Jan-01 80 2 Bob Math Feb-15 75 3 Carol Science Jan-05 60 3 Carol Science Mar-10 90 Data Structure: exams(student_id, name, subject, date, score) Improvement Criteria: 1. Same subject, 2+ exams 2. Latest score {'>'} First score ALGORITHM STEPS 1 Group by Student+Subject Partition data by student_id and subject combination 2 Order by Date Sort exams chronologically within each group 3 Find First and Last Scores Use FIRST_VALUE/LAST_VALUE window functions 4 Compare and Filter Keep students where last_score {'>'} first_score Alice Math First: 70 Last: 85 [OK] Bob Math First: 80 Last: 75 [NO] Carol Science First: 60 Last: 90 [OK] FINAL RESULT Students Who Improved: A UP Alice Subject: Math 70 --{'>'} 85 (+15 pts) IMPROVED C UP Carol Subject: Science 60 --{'>'} 90 (+30 pts) IMPROVED Output Array: [{Alice, Math}, {Carol, Science}] 2 of 3 students improved Bob did not improve (80 to 75) Key Insight: Use SQL window functions (FIRST_VALUE, LAST_VALUE) with PARTITION BY student_id, subject and ORDER BY exam_date to efficiently compare first and last scores without self-joins. This optimal approach has O(n log n) complexity due to sorting, better than O(n^2) nested queries. TutorialsPoint - Find Students Who Improved | Optimal Solution

Related Problems

Asked in
Amazon 23 Microsoft 18 Google 15
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