Top N Students by Subject | Dataford Interview Questions

How would you retrieve the top N students for a given subject?

Write a PostgreSQL query against the students, subjects, and student_scores tables. For this exercise, use Mathematics as the subject and return the top 3 students. Scores that are NULL must be ignored.

Output

One row per qualifying student, with student_id, student_name, subject_name, score, and position.
Include only the requested subject and the top 3 positions.
Order by position ascending. Break equal scores by lower student_id.

Schema

students

Column	Type	Description
student_idPK	INTEGER	Unique student identifier
student_name	VARCHAR(100)	Student full name

subjects

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

student_scores

Column	Type	Description
score_idPK	INTEGER	Unique score record identifier
student_id	INTEGER	Referenced student identifier
subject_id	INTEGER	Referenced subject identifier
score	NUMERIC(5,2)	Student score for the subject

Your question is Top N Students by Subject. 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