Employee Count by Department | Dataford Interview Questions

Synchrony needs a workforce report for departments supporting products such as Synchrony Care. Write a PostgreSQL query that counts active employees by department.

Requirements

Return every department, including departments with no active employees.
Count only employees whose is_active value is TRUE.
Return department_name and active_employee_count, ordered alphabetically by department name.

Place the active-employee condition in the join so that departments with no matching active employees remain in the result. Employees without a valid department should not create an additional department row.

Schema

departments

Column	Type	Description
department_idPK	INT	Primary key for the department
department_name	VARCHAR(100)	Department name

employees

Column	Type	Description
employee_idPK	INT	Primary key for the employee
employee_name	VARCHAR(100)	Employee's name
department_id	INT	Assigned department identifier
is_active	BOOLEAN	Whether the employee is currently active

Your question is Employee Count by Department. 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