Top Departments by Enrollment | Dataford Interview Questions

Your question is Top Departments by Enrollment. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Columbia University wants to compare department enrollment levels across academic semesters. Write a PostgreSQL query that identifies the top three departments by average enrollment per semester.

Requirements

Join departments, courses, and enrollments to associate students with departments.
Count enrollments for each department and semester, excluding rows with a missing semester.
Calculate each department's average semester enrollment and rank departments with RANK().
Return departments with ranks 1 through 3, including ties, ordered by rank and department name.

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Columbia University department name

courses

Column	Type	Description
course_idPK	INT	Unique course identifier
department_id	INT	Department that offers the course
course_title	VARCHAR(150)	Course title

enrollments

Column	Type	Description
enrollment_idPK	INT	Unique enrollment identifier
course_id	INT	Course associated with the enrollment
student_name	VARCHAR(100)	Name of the enrolled student
semester	VARCHAR(20)	Academic semester for the enrollment