Top Departments YoY Growth | Dataford Interview Questions

Write a SQL query to find the top 5 departments by enrollment growth year-over-year at The Ohio State University, utilizing window functions.

Compare each department's latest available academic year with its immediately preceding year. Rank departments by absolute enrollment increase, using percentage growth and department name as deterministic tie-breakers.

Output

One row per department in the top five.
Columns: department_name, academic_year, current_enrollment, prior_enrollment, enrollment_growth, growth_percentage.
Sort by enrollment_growth descending, growth_percentage descending, then department_name ascending.

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Official department name
college_name	VARCHAR(100)	Associated college or academic division

annual_department_enrollment

Column	Type	Description
department_id	INT	Department associated with the enrollment record
academic_year	INT	Academic year represented as its ending year
enrollment	INT	Total enrolled students for the department and academic year

Your question is Top Departments YoY Growth. 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