SQL for Second Highest Marks | Dataford Interview Questions

Yum! Brands uses Yum Academy assessments to monitor employee readiness across its restaurant brands. Write a PostgreSQL query to identify the student or students with the second-highest marks in the Restaurant Operations Assessment.

Requirements

Join students to their assessment scores and the assessment definition.
Rank distinct non-null marks from highest to lowest using a window function.
Return every student whose marks are the second-highest distinct score, ordered by student name.

Schema

students

Column	Type	Description
student_idPK	INT	Unique student identifier
student_name	VARCHAR(100)	Student's full name
brand	VARCHAR(50)	Yum! Brands restaurant brand

assessments

Column	Type	Description
assessment_idPK	INT	Unique assessment identifier
assessment_name	VARCHAR(150)	Assessment title

assessment_scores

Column	Type	Description
score_idPK	INT	Unique score record
student_id	INT	Student receiving the score
assessment_id	INT	Assessment being scored
marks	INT	Assessment marks from 0 to 100

Your question is SQL for Second Highest Marks. 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