Second Highest Value Query | Dataford Interview Questions

Interactive Brokers uses SQL assessments when evaluating engineers who may support Trader Workstation and Client Portal systems. Write a PostgreSQL query to identify candidates who achieved the second-highest distinct mark on the SQL-CORE assessment.

Requirements

Consider only graded SQL-CORE records with non-NULL marks.
Rank distinct marks from highest to lowest and return the second-highest mark.
Return every candidate tied at that mark, including candidate_id, candidate_name, and marks.
Sort the result by candidate_id in ascending order.

Schema

candidates

Column	Type	Description
candidate_idPK	INT	Unique candidate identifier
candidate_name	VARCHAR(100)	Candidate's full name
hiring_track	VARCHAR(50)	Engineering hiring track

assessment_scores

Column	Type	Description
score_idPK	INT	Unique score record identifier
candidate_id	INT	Candidate associated with the score
exam_code	VARCHAR(20)	Assessment identifier
marks	NUMERIC(5,2)	Assessment marks out of 100

Your question is Second Highest Value Query. 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