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.
Quick Answer: 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.