Student Second Maximum Score | Dataford Interview Questions

BharatPe's learning and onboarding program stores students and their exam attempts. Write a PostgreSQL query to identify students whose best score is the second-highest distinct score across all students.

Requirements

Calculate each student's maximum non-NULL score across all attempts.
Rank distinct maximum scores in descending order, so tied students share the same rank.
Return every student at rank 2 with their student ID, name, and maximum score.
Exclude students who have no valid scored attempt, and order the result by student_id.

Schema

students

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

exam_scores

Column	Type	Description
attempt_idPK	INTEGER	Unique exam attempt identifier
student_id	INTEGER	Logical reference to the student who made the attempt
score	INTEGER	Attempt score, which may be NULL when not evaluated

Your question is Student Second Maximum Score. Start with the requirements and the two 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