Top SQL Join and Grouping | Dataford Interview Questions

Nomura's internal learning platform stores students and the divisions in which they are enrolled. Write a PostgreSQL query that returns the three highest-scoring students within every division.

Requirements

Join students to divisions using division_id.
Rank students separately within each division by score descending, with student_id ascending as the tie-breaker.
Return only ranks 1 through 3, excluding students without a matching division.
Sort the final result by division name and rank.

Schema

divisions

Column	Type	Description
division_idPK	INTEGER	Unique division identifier
division_name	VARCHAR(100)	Nomura division name

students

Column	Type	Description
student_idPK	INTEGER	Unique student identifier
student_name	VARCHAR(100)	Student's name
division_id	INTEGER	References divisions.division_id
score	INTEGER	Assessment score

Your question is Top SQL Join and Grouping. 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