Write SQL for top student per department
Company: Point72
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Schema:
Departments(dept_id PK, dept_name)
Students(student_id PK, student_name, dept_id FK nullable, gpa numeric(3,2) nullable, enrolled_at date)
Sample data:
Departments
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | CS |
| 2 | Math |
| 3 | Physics |
| 4 | History |
+---------+-----------+
Students
+------------+--------------+---------+------+------------+
| student_id | student_name | dept_id | gpa | enrolled_at|
+------------+--------------+---------+------+------------+
| 101 | Alice | 1 | 3.90 | 2022-09-01 |
| 102 | Bob | 1 | 3.90 | 2023-01-15 |
| 103 | Cara | 2 | 3.85 | 2021-09-01 |
| 104 | Dan | 2 | NULL | 2024-02-01 |
| 105 | Eve | NULL | 3.70 | 2023-09-01 |
| 106 | Frank | 3 | 3.90 | 2020-09-01 |
+------------+--------------+---------+------+------------+
Write a single SQL query to list, for every department (including those with no students), the department name and the top student by GPA; break GPA ties by earliest enrolled_at, then by smallest student_id. If a department has no students with non-NULL GPA, return one row with student fields NULL.
Output columns: dept_id, dept_name, student_id, student_name, gpa, enrolled_at.
Show the expected result for the sample data.
Overview: This question evaluates proficiency in SQL query formulation, including group-wise selection, joins, NULL handling, deterministic tie-breaking and ordering to identify a top-per-group student.
You are given two tables: Departments and Students. For every department (including departments with no students at all, or with only students whose GPA is NULL), return one row with the department and its top student by GPA. If multiple students in a department share the same highest GPA, break ties by earliest enrolled_at, then by smallest student_id. If a department has no students with a non-NULL GPA, return one row for that department with all student-related fields set to NULL.
Output columns: dept_id, dept_name, student_id, student_name, gpa, enrolled_at.
Tables
Departments(dept_id INT, dept_name VARCHAR(50))
Students(student_id INT, student_name VARCHAR(100), dept_id INT, gpa NUMERIC(3,2), enrolled_at DATE)
Hints
- Start from Departments and LEFT JOIN to a derived table of Students so that departments without qualifying students still appear.
- Use ROW_NUMBER() partitioned by dept_id and ordered by GPA DESC, enrolled_at ASC, then student_id ASC to pick the top student per department.