SQL for Course Enrollment Metrics | Dataford Interview Questions

Your question is SQL for Course Enrollment Metrics. 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

Patreon’s internal creator education catalog needs a report showing which active professors are associated with each department and which published courses they teach. Write a PostgreSQL query using the relationships among professors, departments, and courses.

Requirements

Return every active professor, including professors with no published courses or no assigned department.
Display the professor name, department name, number of published courses, and a comma-separated alphabetical list of published course titles.
Show 0 and None when an active professor has no published courses.
Order by published course count descending, then professor name ascending.

Schema

professors

Column	Type	Description
professor_idPK	INT	Unique professor identifier
professor_name	VARCHAR(100)	Professor’s name
department_id	INT	References departments.department_id
is_active	BOOLEAN	Whether the professor is active

departments

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

courses

Column	Type	Description
course_idPK	INT	Unique course identifier
course_title	VARCHAR(150)	Course title
professor_id	INT	References professors.professor_id
status	VARCHAR(20)	Course publication status