Find Managers with 4+ Direct Reports

Problem Statement:

Write a query to display the "full_name" of a manager who manages 4 or more employees.

Note:

• Return the result ordered by full_name in ascending order.

• The column manager_id in the employees table represents the employee_id of the manager.

  Dataset Description:

Sample Input:

Table: employees

Sample Output:

Approach 1: Using a Common Table Expression (CTE)

WITH
    ManagersWithSubordinates AS (
        SELECT
            m.employee_id
        FROM
            employees m
            INNER JOIN employees e ON m.employee_id = e.manager_id
        GROUP BY
            m.employee_id
        HAVING
            COUNT(*) > 3
    )
SELECT
    CONCAT (m1.first_name, ' ', m1.last_name) AS full_name
FROM
    employees m1
WHERE
    m1.employee_id IN (
        SELECT
            employee_id
        FROM
            ManagersWithSubordinates
    )
ORDER BY
    full_name;

Explanation:

• CTE (ManagersWithSubordinates): Identifies managers who have more than 3 subordinates.

• Main Query: Retrieves full names of employees who are managers identified in the CTE.

• ORDER BY: Sorts the results by full_name.

Approach 2: Using EXISTS Clause

SELECT
    CONCAT (m1.first_name, ' ', m1.last_name) AS full_name
FROM
    employees m1
WHERE
    EXISTS (
        SELECT
            1
        FROM
            employees m
            INNER JOIN employees e ON m.employee_id = e.manager_id
        WHERE
            m.employee_id = m1.employee_id
        GROUP BY
            m.employee_id
        HAVING
            COUNT(*) > 3
    )
ORDER BY
    full_name;

Explanation:

• EXISTS Clause: Checks if there exists a manager (m) who has more than 3 subordinates (e).

• Main Query: Retrieves full names of employees who meet the criteria specified in the EXISTS clause.

• ORDER BY: Sorts the results by full_name.

Approach 3: Using Subquery

select
    concat (m1.first_name, ' ', m1.last_name) as full_name
from
    employees m1
where
    m1.employee_id in (
        select
            m.employee_id
        from
            employees m
            inner join employees e on m.employee_id = e.manager_id
        group by
            m.employee_id
        having
            count(*) > 3
    )
order by
    full_name

Explaination

• WHERE Clause:

  • m1.employee_id in (...): Filters the rows to include only those where the employee_id exists in the subquery's result set.

• Subquery Explanation (in WHERE Clause):

  • SELECT m.employee_id FROM ...: This subquery selects employee_id from the employees table (aliased as m) where the employee is a manager (m.employee_id = e.manager_id), joining with another instance of the employees table (aliased as e).

  • GROUP BY m.employee_id HAVING count(*) > 3: Groups the results by employee_id and filters to only include those groups where the count of rows (employees managed by each manager) is greater than 3.

Approach 4: Using INNER JOIN with Subquery

SELECT
    CONCAT (m1.first_name, ' ', m1.last_name) AS full_name
FROM
    employees m1
    JOIN (
        SELECT
            m.employee_id
        FROM
            employees m
            JOIN employees e ON m.employee_id = e.manager_id
        GROUP BY
            m.employee_id
        HAVING
            COUNT(*) > 3
    ) AS ManagersWithSubordinates ON m1.employee_id = ManagersWithSubordinates.employee_id
ORDER BY
    full_name;

Explanation:

• Subquery (ManagersWithSubordinates): Identifies managers who have more than 3 subordinates.

• Main Query: Joins the main employees table (m1) with the subquery to retrieve full names of qualifying managers.

• ORDER BY: Sorts the results by full_name

This article provides SQL queries to display the full names of managers who manage 4 or more employees, with results ordered by full name in ascending order. It explores four approaches: using a Common Table Expression (CTE), an EXISTS clause, a subquery, and an INNER JOIN with a subquery, alongside detailed explanations for each method.