Student Subject Improvement Audit

Medium

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 1

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 2

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 3

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.

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

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 |

[ {"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} ]

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 |

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 |