SQL: Highest Marks Per Department | Dataford Interview Questions

Practo's internal learning dashboard needs to identify the strongest students in each academic department. Write a PostgreSQL query that returns every student whose marks are the highest in their department.

Requirements

Join students with departments to display the department name.
Return all students tied for the highest non-NULL marks in each department.
Exclude departments that have no students with recorded marks.
Order the output by department name and then student name.

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Name of the academic department

students

Column	Type	Description
student_idPK	INT	Unique student identifier
student_name	VARCHAR(100)	Student's full name
department_id	INT	References departments.department_id
marks	INT	Student marks, potentially unrecorded

Your question is SQL: Highest Marks Per Department. 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