Top 3 Marks Per Student | Dataford Interview Questions

The MakeMyTrip QA team wants to identify the strongest performers in its QA Automation assessment. Write a PostgreSQL query that returns every student whose mark is among the top three distinct marks, including all students tied at those marks.

Requirements

Filter results to the QA Automation assessment and exclude NULL marks.
Rank distinct mark values from highest to lowest using a window function.
Join the marks to students and return the student ID, name, assessment name, marks, and rank.
Sort by marks descending, then student ID ascending.

Schema

students

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

student_marks

Column	Type	Description
mark_idPK	INT	Unique mark record identifier
student_id	INT	References students.student_id
assessment_name	VARCHAR(100)	Name of the assessment
marks	INT	Marks earned in the assessment
attempt_no	INT	Assessment attempt number

Your question is Top 3 Marks Per Student. 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