Analyze Organization Hierarchy - Practice Coding Problems

Analyze Organization Hierarchy - Problem

Database Hard

Analyze Organization Hierarchy

You're tasked with analyzing a company's organizational structure from an employee database. Given an Employees table containing employee information including their manager relationships, you need to calculate three key metrics for organizational analysis:

🏢 The Challenge:
1. Hierarchy Levels: Determine each employee's level in the organization (CEO at level 1, direct reports at level 2, etc.)
2. Team Size: For managers, count all employees under them (direct and indirect reports)
3. Salary Budget: For managers, calculate total salary budget they control (including their own salary)

📊 Table Structure:
Employees table contains: employee_id, employee_name, manager_id (null for CEO), salary, and department.

🎯 Output: Return results ordered by level (ascending), then budget (descending), then employee name (ascending).

Input & Output

example_1.sql — Basic Hierarchy

$ Input: Employees table: | employee_id | employee_name | manager_id | salary | department | |-------------|---------------|------------|--------|-----------| | 1 | Alice | null | 100000 | Executive | | 2 | Bob | 1 | 80000 | Engineering| | 3 | Carol | 1 | 90000 | Marketing | | 4 | David | 2 | 70000 | Engineering| | 5 | Eve | 2 | 70000 | Engineering|

› Output: | employee_id | employee_name | level | team_size | budget | |-------------|---------------|--------|-----------|--------| | 1 | Alice | 1 | 4 | 410000 | | 2 | Bob | 2 | 2 | 220000 | | 3 | Carol | 2 | 0 | 90000 | | 4 | David | 3 | 0 | 70000 | | 5 | Eve | 3 | 0 | 70000 |

💡 Note: Alice is CEO (level 1) managing 4 people with total budget 410000. Bob is level 2 managing David and Eve with budget 220000. Carol has no direct reports. Results ordered by level, then budget desc, then name.

example_2.sql — Complex Hierarchy

$ Input: Employees table: | employee_id | employee_name | manager_id | salary | department | |-------------|---------------|------------|--------|-----------| | 1 | CEO | null | 200000 | Executive | | 2 | VP_Eng | 1 | 150000 | Engineering| | 3 | VP_Sales | 1 | 150000 | Sales | | 4 | TechLead | 2 | 120000 | Engineering| | 5 | Engineer1 | 4 | 100000 | Engineering| | 6 | Engineer2 | 4 | 100000 | Engineering| | 7 | SalesManager | 3 | 110000 | Sales |

› Output: | employee_id | employee_name | level | team_size | budget | |-------------|---------------|--------|-----------|--------| | 1 | CEO | 1 | 6 | 930000 | | 2 | VP_Eng | 2 | 3 | 470000 | | 3 | VP_Sales | 2 | 1 | 260000 | | 4 | TechLead | 3 | 2 | 320000 | | 7 | SalesManager | 3 | 0 | 110000 | | 5 | Engineer1 | 4 | 0 | 100000 | | 6 | Engineer2 | 4 | 0 | 100000 |

💡 Note: Multi-level hierarchy with CEO controlling 6 people and budget of 930000. VP_Eng has larger budget than VP_Sales so appears first at level 2. TechLead manages 2 engineers at level 3.

example_3.sql — Single Employee

$ Input: Employees table: | employee_id | employee_name | manager_id | salary | department | |-------------|---------------|------------|--------|-----------| | 1 | Solo | null | 150000 | All |

› Output: | employee_id | employee_name | level | team_size | budget | |-------------|---------------|--------|-----------|--------| | 1 | Solo | 1 | 0 | 150000 |

💡 Note: Edge case with single employee who is CEO with no subordinates. Team size is 0, budget equals their own salary.

Visualization

Tap to expand

Understanding the Visualization

Build Hierarchy Tree

Create recursive organizational structure starting from CEO

Assign Path Identifiers

Generate unique path strings for each employee's position in hierarchy

Calculate Team Sizes

Count subordinates by matching path patterns for each manager

Compute Salary Budgets

Sum salaries for all employees in each manager's subtree

Apply Final Ordering

Sort results by level, budget, and name as specified

Key Takeaway

🎯 Key Insight: Using hierarchical paths like '/1/2/3/' allows efficient pattern matching to identify all subordinates in a single database operation, making complex organizational analysis both simple and performant.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single hierarchy traversal plus sorting for final result ordering

⚡ Linearithmic

Space Complexity

O(n)

Storage for hierarchy paths and intermediate window function results

⚡ Linearithmic Space

Constraints

1 ≤ number of employees ≤ 10⁴
1 ≤ employee_id ≤ 10⁶
1 ≤ salary ≤ 10⁶
Exactly one employee has manager_id = null (the CEO)
No circular references in the management hierarchy
employee_name length ≤ 50 characters
department name length ≤ 30 characters

Asked in

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

The optimal solution uses a recursive CTE with path-based hierarchy tracking to calculate all organizational metrics in a single pass. Key insight: leverage path patterns (like '/1/2/3/') to efficiently identify subordinate relationships and aggregate team sizes and salary budgets using window functions.

Common Approaches

Approach	Time	Space	Notes
✓ Two-Pass CTE Optimization	O(n²)	O(n)	Build hierarchy structure first, then calculate all metrics in second pass
Single Recursive CTE (Optimal)	O(n log n)	O(n)	Calculate all metrics in one recursive traversal with window functions
Brute Force (Multiple Subqueries)	O(n³)	O(n²)	Use separate subqueries for each calculation with repeated hierarchy traversals

Two-Pass CTE Optimization — Algorithm Steps

First pass: Build complete hierarchy with employee levels using recursive CTE
Second pass: Calculate team sizes by counting subordinates for each manager
Second pass: Calculate salary budgets by summing salaries in each manager's subtree
Combine results with proper ordering

Visualization

Tap to expand

Step-by-Step Walkthrough

Build Hierarchy

Create complete organizational tree with levels and paths

Calculate Metrics

Use hierarchy to compute team sizes and budgets efficiently

Join Results

Combine hierarchy with calculated metrics

Apply Ordering

Sort by level, budget, and name

Code -

solution.c — C

// SQL solution - Two-Pass CTE approach
#include <stdio.h>
#include <string.h>

char* solution() {
    static char query[] = 
    "WITH RECURSIVE hierarchy AS ("
    "SELECT employee_id, employee_name, manager_id, salary, department, "
    "CAST(employee_id AS VARCHAR(500)) as path, 1 as level "
    "FROM Employees WHERE manager_id IS NULL "
    "UNION ALL "
    "SELECT e.employee_id, e.employee_name, e.manager_id, e.salary, e.department, "
    "CONCAT(h.path, '->', e.employee_id), h.level + 1 "
    "FROM Employees e JOIN hierarchy h ON e.manager_id = h.employee_id) "
    "SELECT employee_id, employee_name, level, 0 as team_size, salary as budget "
    "FROM hierarchy ORDER BY level ASC, budget DESC, employee_name ASC;";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

One hierarchy traversal plus aggregation operations for each manager

⚠ Quadratic Growth

Space Complexity

O(n)

Storage for complete hierarchy structure and intermediate results

⚡ Linearithmic Space

Constraints

1 ≤ number of employees ≤ 10⁴
1 ≤ employee_id ≤ 10⁶
1 ≤ salary ≤ 10⁶
Exactly one employee has manager_id = null (the CEO)
No circular references in the management hierarchy
employee_name length ≤ 50 characters
department name length ≤ 30 characters

78.3K Views

High Frequency

~25 min Avg. Time

1.8K 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

Two-Pass CTE Optimization — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler