Top Students by Department | Dataford Interview Questions

Write a SQL query that identifies the top 10% of students by GPA within each department.

Use the students and departments tables. Exclude students whose GPA is NULL. For each department, return at least one student when eligible students exist, using student ID as the tie-breaker for an exact cutoff.

Output

One row per selected student, with student_id, student_name, department_name, gpa, and department_rank
Include only students in the top 10% of eligible students in their department
Order by department_name, department_rank, and student_id

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name

students

Column	Type	Description
student_idPK	INT	Unique student identifier
student_name	VARCHAR(100)	Student full name
department_id	INT	Student's department identifier
gpa	NUMERIC(3,2)	Student GPA on a 4.00 scale

Your question is Top Students by 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