JavaScript is required for SkillDojo to work properly. Please enable JavaScript and refresh the page.

Department Headcount Including Subordinates | Algorithm

Medium

Mediumrecursive-cteSQL

Department Headcount Including Subordinates

The HR analytics team is preparing a report to identify managers with significant team sizes across departments for organizational planning. They want to know which managers in each department have at least three employees reporting to them, counting both direct and indirect reports.

Return

manager_name : the name of the manager department_name : the name of the manager's department total_reports : the total number of employees reporting (directly or indirectly) to the manager Include only managers who have at least three total reports. Order the results by department_name (A-Z), then manager_name (A-Z).

Expected output

manager_name	department_name	total_reports
1 row — values hidden, that's the puzzle

Your query should return 1 row with these columns. Your column aliases don't need to match.

Tables · HR — Departments & Employees

departments

idINTEGERpknameTEXT

employees

idINTEGERpknameTEXTdepartment_idINTEGER→departments.idnullmanager_idINTEGER→employees.idnullsalaryINTEGERhired_onTEXT

id	name	department_id	manager_id	salary	hired_on
1	Ana Reyes	1	NULL	150000	2018-03-01
2	Ben Cho	1	1	120000	2019-06-15
3	Cara Diaz	1	1	120000	2020-01-20
4	Dan Ellis	2	NULL	140000	2017-11-05
5	Eve Fox	2	4	95000	2021-02-10

0 of 4 hints revealed

Submit an attempt first.