Query Explanation - 7: Finding Employees Who Manage the Same Number of Employees as Their Manager

 

In this blog post, we’ll go over a query to find employees who manage the same number of subordinates as their own manager does. This scenario might arise in organizations with layered management, where managers oversee teams of similar sizes. By using Common Table Expressions (CTEs) and joins, we can compare employee counts across different levels of management.

Problem Statement

The goal is to identify employees who have the same number of direct reports (subordinates) as their managers. This involves:

1. Counting the number of employees each manager oversees.
2. Comparing the count of direct reports for each employee with their manager’s count.

Example Schema

We’ll work with an Employees table that has the following columns:

1. Employees:
   • EmployeeID (Primary Key): Unique identifier for each employee.
   • ManagerID (Foreign Key): Identifies the manager of each employee.

SQL Query

We need to:

1. Create a CTE to calculate the count of direct reports for each manager.
2. Join this CTE with the Employees table to match employee and manager counts.
3. Use a WHERE clause to filter employees with matching subordinate counts to their managers.

Here’s the query:

WITH EmployeeCounts AS (
    SELECT 
        ManagerID, 
        COUNT(EmployeeID) AS EmployeeCount
    FROM 
        Employees
    GROUP BY 
        ManagerID
)
SELECT 
    e.EmployeeID, 
    e.ManagerID
FROM 
    Employees e
JOIN 
    EmployeeCounts ec1 ON e.EmployeeID = ec1.ManagerID
JOIN 
    EmployeeCounts ec2 ON e.ManagerID = ec2.ManagerID
WHERE 
    ec1.EmployeeCount = ec2.EmployeeCount;

Detailed Breakdown

1. Counting Subordinates for Each Manager:

   • The CTE EmployeeCounts calculates the number of employees (EmployeeCount) each ManagerID directly oversees.
   • COUNT(EmployeeID) is used to determine the number of subordinates for each manager, grouping by ManagerID.

2. Joining with Employee Data:

   • The main query joins the Employees table with EmployeeCounts twice.
     • ec1: This alias represents the subordinate’s count of employees.
     • ec2: This alias represents the manager’s count of employees.
   • JOIN EmployeeCounts ec1 ON e.EmployeeID = ec1.ManagerID: This join links each employee (e.EmployeeID) with their direct report count (ec1.EmployeeCount).
   • JOIN EmployeeCounts ec2 ON e.ManagerID = ec2.ManagerID: This join links each employee’s manager with their subordinate count (ec2.EmployeeCount).

3. Filtering for Matching Counts:

   • The WHERE clause ensures that we only select employees where ec1.EmployeeCount (the employee’s count of direct reports) matches ec2.EmployeeCount (their manager’s count of direct reports).

Example Data

Let’s assume the Employees table has the following data:

 this data:

• Employee 1 manages Employees 2 and 3 (2 direct reports).
• Employee 2 manages Employees 4 and 5 (2 direct reports).
• Employee 3 manages Employees 6 and 7 (2 direct reports).

In this scenario, Employees 2 and 3 have the same number of direct reports as their manager, Employee 1.

Expected Output

The query will return:

Key Concepts

• CTE for Aggregation: Using a CTE (EmployeeCounts) allows us to calculate the employee count for each manager and reuse it in the main query.
• Self-Joins for Comparison: The EmployeeCounts CTE is joined twice to match each employee’s count with their manager’s count.

Business Benefits

This query can help organizations:

1. Identify Team Structure Patterns: Understand if team sizes are consistent across management levels.
2. Balance Workloads: Recognize if there’s a tendency for managers to oversee similar-sized teams.
3. Optimize Management Layers: Provide insights for structuring teams in a way that aligns with organizational goals.