Top Student Per Subject SQL | Dataford Interview Questions

Affirm's quantitative analytics team is reviewing subject-level assessment results for analysts supporting Affirm financing decisions. Write a PostgreSQL query that identifies the top student or students in each subject for the current exam term.

Requirements

Restrict results to the 2026-Spring exam term and exclude records where marks is NULL.
Rank students independently within each subject using their marks in descending order.
Return every student tied for the highest mark, along with the subject name, student ID, student name, and mark.
Sort the final results alphabetically by subject name and then by student ID.

Schema

students

Column	Type	Description
student_idPK	INT	Unique student identifier
student_name	VARCHAR(100)	Student's full name

subjects

Column	Type	Description
subject_idPK	INT	Unique subject identifier
subject_name	VARCHAR(100)	Subject name

exam_scores

Column	Type	Description
score_idPK	INT	Unique score record identifier
student_id	INT	References students.student_id
subject_id	INT	References subjects.subject_id
marks	INT	Marks earned in the assessment
exam_term	VARCHAR(30)	Assessment term

Your question is Top Student Per Subject SQL. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem