Number of Direct Reports
Workday provides human capital management solutions that allow businesses to manage their employees, their roles, and the reporting structures. When Workday consultants start with a new client, one of the first things they’ll do is identify the client’s organizational structure. Each department in a company might have a manager, and under each manager, there are employees reporting directly to them.
You're given a table, employees, with the following columns:
emp_id(integer): Unique identifier for each employee.emp_name(string): Name of the employee.manager_id(integer): Employee ID of the manager to whom this employee reports (can beNULLfor top-level managers).
Write a SQL query that returns the Employee Id and Name of the managers who have a minimum of 2 employees directly reporting to them. Sort the results by the number of direct reports in descending order.
Your output should have the following columns: manager_employee_id, manager_name, number_of_direct_reports
- JOIN Clause: We join the
employeestable with itself. This self-join allows us to pair each employee with their respective manager. Self joins are often used to find relationships within a single table.e.manager_id = m.emp_id: We're making the connection between an employee'smanager_idand the same manager'semp_id.
- GROUP BY Clause: After pairing employees with managers, we group the results by
e.manager_idandm.emp_name(i.e., by each manager's ID and name). - HAVING Clause: Post grouping, we use the
HAVINGclause to filter out groups (i.e., managers) who have fewer than 2 direct reports which we count usingCOUNT. - ORDER BY Clause: Finally, the managers are ordered by the number of their direct reports in descending order
SELECT e.manager_id AS manager_employee_id,
m.emp_name AS manager_name,
COUNT(e.emp_id) AS number_of_direct_reports
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
GROUP BY e.manager_id, m.emp_name
HAVING COUNT(e.emp_id) >= 2
ORDER BY number_of_direct_reports DESC;
SELECT e1.emp_id AS manager_employee_id, e1.emp_name AS manager_name, COUNT(e2.emp_id) AS number_of_direct_reports FROM employees AS e1 INNER JOIN employees AS e2 ON e2.manager_id = e1.emp_id GROUP BY e1.emp_id HAVING COUNT(e2.emp_id) >= 2 ORDER BY number_of_direct_reports DESC, manager_name ASC