Analyze Organization Hierarchy - Problem

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
Organization Hierarchy Analysis OverviewInput: Employeesemp_idnamemgr_idsalary1AliceNULL100002Bob180003Carol26000Hierarchy TreeCEOL2L2L3WITH RECURSIVEHierarchy CTEOutput: Analysis Resultsemp_idnamelevelteambudget1Alice12240002Bob21140003Carol306000Calculated MetricsLevel: Distance from CEOTeam: Count of all subordinatesBudget: Own + subordinates salariesOrdered by: level ASC, budget DESC, name ASC
Understanding the Visualization
1
Input
Employee table with manager relationships
2
Recursive CTE
Build hierarchy levels and find subordinates
3
Output
Level, team size, and budget analysis
Key Takeaway
🎯 Key Insight: Use recursive CTEs to efficiently traverse and analyze hierarchical organizational structures in a single SQL query

Related Problems

Asked in
Amazon 15 Microsoft 12 Google 8 Meta 6
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