Managers with at Least 5 Direct Reports - Practice Coding Problems

Managers with at Least 5 Direct Reports - Problem

Database Medium

Corporate Hierarchy Analysis

You're working as a data analyst for a large corporation and need to identify managers who are overloaded with direct reports. The company wants to find managers who have at least 5 employees reporting directly to them, as these managers might need additional support or organizational restructuring.

Given an Employee table with employee information including their manager relationships, write a SQL query to find all managers with at least 5 direct reports.

Table Schema:
Employee
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
| department | varchar |
| managerId | int |
+-------------+---------+

Key Points:
• id is the primary key
• managerId references another employee's id
• If managerId is null, the employee has no manager
• Return manager names in any order

Input & Output

basic_corporate_hierarchy.sql

$ Input: Employee table: +-----+-------+------------+-----------+ | id | name | department | managerId | +-----+-------+------------+-----------+ | 101 | John | A | null | | 102 | Dan | A | 101 | | 103 | James | A | 101 | | 104 | Amy | A | 101 | | 105 | Anne | A | 101 | | 106 | Ron | B | 101 | | 107 | Bob | B | 101 | | 108 | Alice | B | null | +-----+-------+------------+-----------+

› Output: +------+ | name | +------+ | John | +------+

💡 Note: John is the only manager with at least 5 direct reports. He manages Dan (102), James (103), Amy (104), Anne (105), Ron (106), and Bob (107) - that's 6 direct reports total. Alice has no direct reports.

multiple_managers.sql

$ Input: Employee table: +-----+-------+------------+-----------+ | id | name | department | managerId | +-----+-------+------------+-----------+ | 101 | John | A | null | | 102 | Dan | A | 101 | | 103 | James | A | 101 | | 104 | Amy | A | 101 | | 105 | Anne | A | 101 | | 106 | Ron | B | 101 | | 201 | Alice | B | null | | 202 | Bob | B | 201 | | 203 | Carol | B | 201 | | 204 | David | B | 201 | | 205 | Emma | B | 201 | | 206 | Frank | B | 201 | +-----+-------+------------+-----------+

💡 Note: Both John and Alice have at least 5 direct reports. John manages 5 employees (Dan, James, Amy, Anne, Ron) and Alice manages 5 employees (Bob, Carol, David, Emma, Frank).

no_qualifying_managers.sql

› Output: +------+ | name | +------+ (empty result)

💡 Note: No manager has 5 or more direct reports. John has 2 direct reports (Dan, James) and Alice has 1 direct report (Bob). Since neither manager reaches the threshold of 5, the result is empty.

Visualization

Tap to expand

Understanding the Visualization

Identify Relationships

Map each employee to their direct manager using managerId

Group by Manager

Cluster all employees under their respective managers

Count Team Sizes

Calculate how many direct reports each manager has

Apply Threshold Filter

Keep only managers with 5 or more direct reports

Extract Manager Names

Return the names of qualifying managers

Key Takeaway

🎯 Key Insight: Use SQL's GROUP BY and HAVING clauses to aggregate employee counts by manager and filter efficiently in a single query, avoiding the need for nested loops or multiple passes through the data.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Database optimizes GROUP BY with sorting/hashing, typically O(n log n)

⚡ Linearithmic

Space Complexity

O(n)

Temporary space needed for grouping and aggregation

⚡ Linearithmic Space

Constraints

1 ≤ Employee table rows ≤ 10⁴
id is unique for each employee
managerId references a valid employee id or is null
No employee is their own manager
Employee names are unique within the company
Result can be returned in any order

Asked in

a Amazon 45 ⊞ Microsoft 38 G Google 32 f Meta 28 Apple 22 in LinkedIn 19

The optimal solution uses GROUP BY with HAVING clause in SQL to efficiently aggregate employee counts per manager. By joining the Employee table with itself and grouping by manager details, we can count direct reports in a single query. The HAVING COUNT(*) >= 5 clause filters managers with sufficient reports, making this approach both elegant and performant with O(n log n) time complexity leveraging database optimizations.

Common Approaches

Approach	Time	Space	Notes
✓ GROUP BY with HAVING (Optimal)	O(n log n)	O(n)	Use GROUP BY to count direct reports and HAVING to filter efficiently
Brute Force (Nested Queries)	O(n²)	O(1)	For each employee, count their direct reports with a subquery

GROUP BY with HAVING (Optimal) — Algorithm Steps

JOIN Employee table with itself on managerId = id
GROUP BY manager id and name to aggregate reports
Use COUNT(*) to count direct reports per manager
Apply HAVING COUNT(*) >= 5 to filter managers
SELECT manager name from the results

Visualization

Tap to expand

Step-by-Step Walkthrough

Join Tables

Join Employee table with itself on managerId = id

Group by Manager

Group all employees by their manager

Count Reports

Count employees in each group

Filter with HAVING

Keep only groups with count >= 5

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define MAX_MANAGERS 1000

typedef struct {
    int managerId;
    char name[50];
    int count;
} ManagerCount;

char** findManagersOptimal(Employee* employees, int size, int* returnSize) {
    ManagerCount managers[MAX_MANAGERS];
    int managerCount = 0;
    
    // Simulate JOIN and GROUP BY
    for (int i = 0; i < size; i++) {
        if (employees[i].managerId != -1) {
            int managerId = employees[i].managerId;
            
            // Find if manager already exists in our count
            int found = -1;
            for (int j = 0; j < managerCount; j++) {
                if (managers[j].managerId == managerId) {
                    found = j;
                    break;
                }
            }
            
            if (found == -1) {
                // New manager, find their name
                for (int k = 0; k < size; k++) {
                    if (employees[k].id == managerId) {
                        managers[managerCount].managerId = managerId;
                        strcpy(managers[managerCount].name, employees[k].name);
                        managers[managerCount].count = 1;
                        managerCount++;
                        break;
                    }
                }
            } else {
                managers[found].count++;
            }
        }
    }
    
    // Filter managers with >= 5 reports (HAVING clause)
    char** result = malloc(managerCount * sizeof(char*));
    *returnSize = 0;
    
    for (int i = 0; i < managerCount; i++) {
        if (managers[i].count >= 5) {
            result[*returnSize] = malloc(strlen(managers[i].name) + 1);
            strcpy(result[*returnSize], managers[i].name);
            (*returnSize)++;
        }
    }
    
    return result;
}

/* SQL equivalent:
SELECT m.name
FROM Employee e
JOIN Employee m ON e.managerId = m.id
GROUP BY m.id, m.name
HAVING COUNT(*) >= 5
*/

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Database optimizes GROUP BY with sorting/hashing, typically O(n log n)

⚡ Linearithmic

Space Complexity

O(n)

Temporary space needed for grouping and aggregation

⚡ Linearithmic Space

Constraints

1 ≤ Employee table rows ≤ 10⁴
id is unique for each employee
managerId references a valid employee id or is null
No employee is their own manager
Employee names are unique within the company
Result can be returned in any order

52.3K Views

High Frequency

~15 min Avg. Time

1.9K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

GROUP BY with HAVING (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler