Write SQL for top student per department

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.

Top student per department (including empty departments)

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.

Table Departments

column	type	constraints
dept_id	INT	PRIMARY KEY, NOT NULL
dept_name	VARCHAR(50)	NOT NULL

Sample data:

dept_id	dept_name
1	CS
2	Math
3	Physics
4	History

Table Students

column	type	constraints
student_id	INT	PRIMARY KEY, NOT NULL
student_name	VARCHAR(100)	NOT NULL
dept_id	INT	FOREIGN KEY REFERENCES Departments(dept_id)
gpa	NUMERIC(3,2)
enrolled_at	DATE	NOT NULL

Sample data:

dept_id	enrolled_at	gpa	student_id	student_name
1	2022-09-01	3.9	101	Alice
1	2023-01-15	3.9	102	Bob
2	2021-09-01	3.85	103	Cara
2	2024-02-01	NULL	104	Dan
NULL	2023-09-01	3.7	105	Eve
3	2020-09-01	3.9	106	Frank

Expected output

One row per department, including departments with no students or no non-NULL GPAs, with the top student chosen by GPA (DESC), then earliest enrolled_at, then smallest student_id. If no qualifying student, student-related columns are NULL.

dept_id	dept_name	enrolled_at	gpa	student_id	student_name
1	CS	2022-09-01	3.9	101	Alice
2	Math	2021-09-01	3.85	103	Cara
3	Physics	2020-09-01	3.9	106	Frank
4	History	NULL	NULL	NULL	NULL

Hints

1. Start from Departments and LEFT JOIN to a derived table of Students so that departments without qualifying students still appear.
2. 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.