SQL Top Retention With Window Functions | Dataford Interview Questions

Write a SQL query to find the top 5 departments by student retention rate using window functions for UNC Chapel Hill.

Use the provided student, department, and enrollment records. Define retention as enrollment in both 2023-Fall and 2024-Fall. Exclude departments without students in the starting term.

Output

One row per department, limited to the five highest retention rates.
Columns: department_id, department_name, starting_students, retained_students, retention_rate, and department_rank.
Sort by retention rate descending, then department ID ascending for ties.

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	UNC Chapel Hill department name

students

Column	Type	Description
student_idPK	INT	Unique student identifier
department_id	INT	Student's department

enrollments

Column	Type	Description
enrollment_idPK	INT	Unique enrollment record identifier
student_id	INT	Enrolled student
term	VARCHAR(20)	Academic enrollment term

Your question is SQL Top Retention With Window Functions. Start with the requirements and the three 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