Student Subject Improvement Audit

MEDIUM20 pts

An academic analytics team wants to identify student-subject combinations with measurable score progress over time.

Table: Scores

student_id: student identifier
subject: subject name
score: exam score between 0 and 100
exam_date: exam date in YYYY-MM-DD format

Each row records one student's score for one subject on one date. (student_id, subject, exam_date) is unique.

Task: For each (student_id, subject), compute:

first_score: score at the earliest exam_date
latest_score: score at the latest exam_date

Keep only pairs that satisfy both conditions:

at least two exam records in that subject
latest_score is strictly greater than first_score

Output requirements:

Return columns exactly in this order: student_id, subject, first_score, latest_score
Sort by student_id ASC, subject ASC.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Scores:
| 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":101,"subject":"Math","first_score":70,"latest_score":85},
  {"student_id":102,"subject":"Math","first_score":80,"latest_score":85},
  {"student_id":104,"subject":"Physics","first_score":75,"latest_score":85}
]

Explanation

Only subject tracks with at least two attempts and strict first-to-latest improvement are returned.

Example

Input

Scores:
| student_id | subject   | score | exam_date  |
|------------|-----------|-------|------------|
| 210        | Biology   | 55    | 2024-01-10 |
| 210        | Biology   | 55    | 2024-03-10 |
| 210        | Chemistry | 60    | 2024-01-10 |
| 210        | Chemistry | 74    | 2024-02-20 |
| 210        | Chemistry | 68    | 2024-04-05 |

Output

[
  {"student_id":210,"subject":"Chemistry","first_score":60,"latest_score":68}
]

Explanation

Biology is excluded because the latest score is not greater than the first score. Intermediate peaks do not matter; only first and latest are compared.

Example

Input

Scores:
| student_id | subject | score | exam_date  |
|------------|---------|-------|------------|
| 501        | History | 88    | 2023-05-01 |
| 501        | History | 91    | 2023-02-01 |
| 501        | History | 90    | 2023-08-01 |
| 502        | History | 40    | 2023-01-01 |
| 502        | History | 45    | 2023-09-01 |

Output

[
  {"student_id":502,"subject":"History","first_score":40,"latest_score":45}
]

Explanation

Rows can arrive unsorted. first_score and latest_score must be computed by exam_date, not by input order.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Scores <= 250000
1 <= student_id <= 1000000000
subject is a non-empty string
0 <= score <= 100
exam_date format is YYYY-MM-DD
(student_id, subject, exam_date) is unique within a test case
first_score uses earliest exam_date per (student_id, subject)
latest_score uses latest exam_date per (student_id, subject)
A row qualifies only if attempt_count >= 2 and latest_score > first_score
Output columns must be exactly: student_id, subject, first_score, latest_score
Final ordering must be student_id ASC, subject ASC
SQL submissions are evaluated with SQLite 3.27.2
Pandas submissions must return a DataFrame with required columns

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Scores =

[{"student_id":101,"subject":"Math","score":70,"exam_date":"2023-01-15"},{"student_id":101,"subject":"Math","score":85,"exam_date":"2023-02-15"},{"student_id":101,"subject":"Physics","score":65,"exam_date":"2023-01-15"},{"student_id":101,"subject":"Physics","score":60,"exam_date":"2023-02-15"},{"student_id":102,"subject":"Math","score":80,"exam_date":"2023-01-15"},{"student_id":102,"subject":"Math","score":85,"exam_date":"2023-02-15"},{"student_id":103,"subject":"Math","score":90,"exam_date":"2023-01-15"},{"student_id":104,"subject":"Physics","score":75,"exam_date":"2023-01-15"},{"student_id":104,"subject":"Physics","score":85,"exam_date":"2023-02-15"}]

Student Subject Improvement Audit

Hints

Student Subject Improvement Audit

Hints