Top Departments YoY Growth

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

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

Output details

• Use the maximum academic year across all enrollment records as the single current year for every department.
• Round growth_percentage to two decimal places.
• Exclude departments whose current_enrollment or prior_enrollment is NULL.
• Treat a zero prior_enrollment as producing a NULL growth_percentage rather than a calculable percentage.