All People Report to the Given Manager - Practice Coding Problems

All People Report to the Given Manager - Problem

Database Medium

Company Hierarchy Reporting Structure

You're working with a company's employee database that tracks the reporting structure. The database contains information about employees and their direct managers, forming a hierarchical tree structure.

Given an Employees table where each employee has an ID, name, and manager ID, your task is to find all employees who report to the company head (employee with ID = 1), either directly or indirectly.

The company guarantees that the reporting chain won't exceed 3 levels deep, making this a manageable tree traversal problem.

Goal: Return the employee IDs of all people who report to the head of the company.

Table Structure:
Employees
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| employee_id | int |
| employee_name | varchar |
| manager_id | int |
+---------------+---------+

Input & Output

example_1.sql — Basic Hierarchy

$ Input: Employees table: +-------------+---------------+------------+ | employee_id | employee_name | manager_id | +-------------+---------------+------------+ | 1 | Boss | 1 | | 3 | Alice | 3 | | 2 | Bob | 1 | | 4 | Daniel | 2 | | 7 | Luis | 4 | | 8 | Jhon | 3 | | 9 | Angela | 8 | | 77 | Robert | 1 | +-------------+---------------+------------+

› Output: [2, 4, 7, 8, 9, 77]

💡 Note: The head of the company is employee with ID 1. Direct reports: Bob(2) and Robert(77). Bob's report: Daniel(4). Daniel's report: Luis(7). Alice(3) reports to herself, so her subordinates also count: Jhon(8) and Angela(9) who reports to Jhon.

example_2.sql — Simple Chain

$ Input: Employees table: +-------------+---------------+------------+ | employee_id | employee_name | manager_id | +-------------+---------------+------------+ | 1 | CEO | 1 | | 2 | VP | 1 | | 3 | Director | 2 | | 4 | Manager | 3 | +-------------+---------------+------------+

› Output: [2, 3, 4]

💡 Note: Simple hierarchy chain: CEO(1) → VP(2) → Director(3) → Manager(4). All employees except CEO report to the head either directly or indirectly.

example_3.sql — Only Direct Reports

$ Input: Employees table: +-------------+---------------+------------+ | employee_id | employee_name | manager_id | +-------------+---------------+------------+ | 1 | Head | 1 | | 2 | VP1 | 1 | | 3 | VP2 | 1 | | 4 | VP3 | 1 | +-------------+---------------+------------+

› Output: [2, 3, 4]

💡 Note: Flat structure where all VPs report directly to the head. No indirect reports exist.

Visualization

Tap to expand

Understanding the Visualization

Start at the Top

Begin with the head of company (employee_id = 1)

Find Direct Reports

Identify all employees with manager_id = 1

Expand the Search

For each found employee, find their direct reports

Continue Recursively

Keep expanding until no more subordinates are found

Collect Results

Return all employee IDs found in the traversal

Key Takeaway

🎯 Key Insight: Using recursive CTE allows us to traverse the entire hierarchy in one elegant query, making it both efficient and maintainable for any depth of organizational structure.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Each employee is visited exactly once during the traversal

✓ Linear Growth

Space Complexity

O(d)

Space complexity is O(d) where d is the maximum depth of the hierarchy (recursion stack)

✓ Linear Space

Constraints

1 ≤ n ≤ 1000 (number of employees)
1 ≤ employee_id ≤ 10⁹
employee_id is unique for each employee
The head of the company has employee_id = 1
The reporting chain will not exceed 3 levels deep
manager_id references a valid employee_id or the employee's own ID

Asked in

a Amazon 45 ⊞ Microsoft 38 G Google 32 f Meta 28

The optimal solution uses SQL's Recursive Common Table Expression (CTE) to elegantly traverse the corporate hierarchy. Starting from the head (ID=1), it recursively finds all direct and indirect reports in a single query with O(n) time complexity. The WITH RECURSIVE clause makes this both readable and efficient.

Common Approaches

Approach	Time	Space	Notes
✓ Multiple Self-Joins (Brute Force)	O(n³)	O(n)	Use separate joins for each level of hierarchy
Recursive CTE (Optimal)	O(n)	O(d)	Use SQL Common Table Expression with recursion to traverse hierarchy efficiently

Multiple Self-Joins (Brute Force) — Algorithm Steps

Find all employees who directly report to manager ID = 1
Find all employees who report to the level-1 managers
Find all employees who report to the level-2 managers
Union all results together

Visualization

Tap to expand

Step-by-Step Walkthrough

Find Level 1

Identify all direct reports to CEO (manager_id = 1)

Find Level 2

For each Level 1 manager, find their direct reports

Find Level 3

For each Level 2 manager, find their direct reports

Union Results

Combine all employees from all levels

Code -

solution.c — C

/* SQL Query Implementation */
const char* query = 
"SELECT DISTINCT e1.employee_id\n"
"FROM Employees e1\n"
"WHERE e1.manager_id = 1\n"
"\n"
"UNION\n"
"\n"
"SELECT DISTINCT e2.employee_id\n"
"FROM Employees e1\n"
"JOIN Employees e2 ON e1.employee_id = e2.manager_id\n"
"WHERE e1.manager_id = 1\n"
"\n"
"UNION\n"
"\n"
"SELECT DISTINCT e3.employee_id\n"
"FROM Employees e1\n"
"JOIN Employees e2 ON e1.employee_id = e2.manager_id\n"
"JOIN Employees e3 ON e2.employee_id = e3.manager_id\n"
"WHERE e1.manager_id = 1\n"
"\n"
"ORDER BY employee_id;";

/* For demonstration */
int* bruteForceHierarchy(Employee* employees, int size, int* returnSize) {
    int* result = malloc(size * sizeof(int));
    int count = 0;
    
    // Level 1: Direct reports
    int level1[100], level1_count = 0;
    for (int i = 0; i < size; i++) {
        if (employees[i].manager_id == 1) {
            level1[level1_count++] = employees[i].employee_id;
            result[count++] = employees[i].employee_id;
        }
    }
    
    // Level 2 and 3
    for (int i = 0; i < level1_count; i++) {
        int level2[100], level2_count = 0;
        for (int j = 0; j < size; j++) {
            if (employees[j].manager_id == level1[i]) {
                level2[level2_count++] = employees[j].employee_id;
                result[count++] = employees[j].employee_id;
            }
        }
        
        for (int k = 0; k < level2_count; k++) {
            for (int j = 0; j < size; j++) {
                if (employees[j].manager_id == level2[k]) {
                    result[count++] = employees[j].employee_id;
                }
            }
        }
    }
    
    *returnSize = count;
    return result;
}

Time & Space Complexity

Time Complexity

⏱️

O(n³)

In worst case, we might scan the table multiple times for each level

⚠ Quadratic Growth

Space Complexity

O(n)

Space for storing intermediate results from each level

⚡ Linearithmic Space

Constraints

1 ≤ n ≤ 1000 (number of employees)
1 ≤ employee_id ≤ 10⁹
employee_id is unique for each employee
The head of the company has employee_id = 1
The reporting chain will not exceed 3 levels deep
manager_id references a valid employee_id or the employee's own ID

24.7K Views

Medium Frequency

~15 min Avg. Time

892 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

Multiple Self-Joins (Brute Force) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler