Analyze Organization Hierarchy

Analyze Organization Hierarchy - Problem

Database Hard

Given an Employees table containing information about employees and their managers, analyze the organizational hierarchy to determine:

Hierarchy Levels: Each employee's level in the organization (CEO is level 1, direct reports to CEO are level 2, etc.)
Team Size: For each manager, count total employees under them (direct and indirect reports)
Salary Budget: For each manager, calculate total salary budget they control (all reports' salaries plus their own)

Return results ordered by level ascending, then budget descending, then employee_name ascending.

Table Schema

Employees

Column Name	Type	Description
`employee_id` PK	int	Unique identifier for each employee
`employee_name`	varchar	Name of the employee
`manager_id`	int	ID of the employee's manager (NULL for CEO)
`salary`	int	Employee's salary
`department`	varchar	Department where employee works

Primary Key: employee_id

Note: manager_id is NULL for the top-level manager (CEO). Each employee reports to exactly one manager except the CEO.

Input & Output

Example 1 — Basic Organization Hierarchy

Input Table:

employee_id	employee_name	manager_id	salary	department
1	Alice		10000	IT
2	Bob	1	8000	IT
3	Carol	2	6000	IT
4	David	1	7000	HR
5	Eve	4	5000	HR

Output:

employee_id	employee_name	level	team_size	budget
1	Alice	1	4	36000
2	Bob	2	1	14000
4	David	2	1	12000
3	Carol	3	0	6000
5	Eve	3	0	5000

💡 Note:

Alice is the CEO (level 1) with 4 total subordinates and controls the entire company budget of 36000. Bob and David are level 2 managers with 1 subordinate each. Carol and Eve are level 3 employees with no subordinates.

Example 2 — Single Employee Organization

Input Table:

employee_id	employee_name	manager_id	salary	department
1	John		15000	CEO

Output:

employee_id	employee_name	level	team_size	budget
1	John	1	0	15000

💡 Note:

John is the only employee and CEO, so he's at level 1 with no team members and only controls his own salary as budget.

Example 3 — Flat Organization Structure

Input Table:

employee_id	employee_name	manager_id	salary	department
1	Manager		12000	Management
2	Alice	1	8000	Sales
3	Bob	1	8500	Marketing
4	Carol	1	7500	Support

Output:

employee_id	employee_name	level	team_size	budget
1	Manager	1	3	36000
3	Bob	2	0	8500
2	Alice	2	0	8000
4	Carol	2	0	7500

💡 Note:

Flat organization with Manager at level 1 controlling 3 direct reports. Level 2 employees are ordered by budget descending (Bob > Alice > Carol), then by name alphabetically.

Constraints

1 ≤ employee_id ≤ 1000
1 ≤ employee_name.length ≤ 100
1000 ≤ salary ≤ 100000
manager_id is either NULL (for CEO) or references a valid employee_id
1 ≤ department.length ≤ 50

Visualization

Tap to expand

Asked in

A Amazon 15 M Microsoft 12 G Google 8 M Meta 6

Use recursive CTE to traverse the organizational hierarchy tree, calculating level from CEO downward, then aggregate team_size and budget for each manager by finding all their direct and indirect reports.

Table Schema

Employees

Column Name	Type	Description
`employee_id` PK	int	Unique identifier for each employee
`employee_name`	varchar	Name of the employee
`manager_id`	int	ID of the employee's manager (NULL for CEO)
`salary`	int	Employee's salary
`department`	varchar	Department where employee works

Primary Key: employee_id

Note: manager_id is NULL for the top-level manager (CEO). Each employee reports to exactly one manager except the CEO.

Common Approaches

✓ Recursive CTE with Hierarchy Traversal

⏱️ Time: O(n²) Space: O(n)

First, use recursive CTE to determine each employee's level in the hierarchy. Then, for each manager, recursively find all subordinates to calculate team size and total budget controlled.

Recursive CTE with Hierarchy Traversal — Algorithm Steps

Step 1: Create recursive CTE to find hierarchy levels starting from CEO
Step 2: Create another recursive CTE to find all subordinates for each manager
Step 3: Calculate team size and budget totals, then order results

Visualization

Tap to expand

Step-by-Step Walkthrough

Find CEO

Start with employee where manager_id IS NULL

Add Direct Reports

Join employees whose manager_id matches current level

Calculate Metrics

Count subordinates and sum salaries recursively

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Each employee may need to traverse all subordinates

⚠ Quadratic Growth

Space Complexity

O(n)

Recursive call stack and temporary results

⚡ Linearithmic Space

23.5K Views

Medium-High Frequency

~25 min Avg. Time

890 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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Recursive CTE with Hierarchy Traversal — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler