CEO Subordinate Hierarchy

CEO Subordinate Hierarchy - Problem

Database Hard

Imagine you're working at a company and need to build a complete organizational chart showing everyone who reports to the CEO, either directly or indirectly. This is a classic hierarchical tree traversal problem that tests your understanding of recursive relationships in databases.

You have an Employees table where each employee has a manager (except the CEO who has NULL as manager_id). Your task is to:

Find all subordinates of the CEO at any level
Calculate their hierarchy level (1 for direct reports, 2 for their reports, etc.)
Compute salary differences from the CEO's salary
Order results by hierarchy level, then by employee ID

This problem combines recursive tree traversal with salary analysis, making it perfect for testing your SQL skills with Common Table Expressions (CTEs) and recursive queries.

Example: If Alice (CEO) manages Bob and Charlie, and Bob manages David, then David has hierarchy_level = 2 and salary_difference = David's salary - Alice's salary.

Input & Output

example_1.sql — Basic Hierarchy

$ Input: Employees = [[1,'Alice',null,150000],[2,'Bob',1,120000],[3,'Charlie',1,110000],[4,'David',2,105000]]

› Output: [[2,'Bob',1,-30000],[3,'Charlie',1,-40000],[4,'David',2,-45000]]

💡 Note: Alice is CEO. Bob and Charlie report directly to Alice (level 1). David reports to Bob (level 2). Salary differences calculated from Alice's 150000.

example_2.sql — Deep Hierarchy

$ Input: Employees = [[1,'CEO',null,200000],[2,'VP',1,150000],[3,'Director',2,120000],[4,'Manager',3,100000],[5,'Employee',4,80000]]

› Output: [[2,'VP',1,-50000],[3,'Director',2,-80000],[4,'Manager',3,-100000],[5,'Employee',4,-120000]]

💡 Note: 5-level hierarchy from CEO to Employee. Each level shows increasing negative salary difference from CEO's 200000.

example_3.sql — Single Employee

$ Input: Employees = [[1,'OnlyCEO',null,100000]]

› Output: []

💡 Note: Only CEO exists with no subordinates, so result is empty (CEO is excluded from output).

Constraints

1 ≤ employees.length ≤ 1000
employee_id is unique for each employee
Exactly one employee has manager_id = NULL (the CEO)
All manager_id values (except CEO) reference valid employee_id
1 ≤ salary ≤ 10⁶
No circular management relationships exist

Visualization

Tap to expand

Understanding the Visualization

Identify CEO

Find the root of hierarchy (manager_id IS NULL)

Recursive Descent

Use CTE to traverse each management level

Track Levels

Increment hierarchy_level for each generation

Calculate Differences

Compute salary gaps from CEO's compensation

Key Takeaway

🎯 Key Insight: Recursive CTEs elegantly solve hierarchical problems by starting with a base case (CEO) and recursively building levels while carrying forward essential data (CEO salary) for calculations.

Asked in

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

The optimal solution uses a Recursive Common Table Expression (CTE) to traverse the organizational hierarchy. Start with the CEO as base case (manager_id IS NULL), then recursively join employees with their managers to build the complete hierarchy tree. Track hierarchy levels by incrementing from parent level and calculate salary differences using the CEO's salary carried through each recursion. This gives us O(n) time complexity with a single elegant query.

Common Approaches

Approach	Time	Space	Notes
✓ Recursive CTE with UNION ALL	O(n)	O(n)	Use recursive Common Table Expression to traverse hierarchy
Nested Subqueries Approach	O(n²)	O(n)	Use multiple nested queries to find each level separately

Recursive CTE with UNION ALL — Algorithm Steps

Create base case: Find CEO (manager_id IS NULL)
Create recursive case: Join employees with their managers from previous iteration
Track hierarchy level by incrementing from parent level
Calculate salary difference using CEO's salary in each iteration
Order final results by hierarchy_level and subordinate_id

Visualization

Tap to expand

Step-by-Step Walkthrough

Base Case

Start with CEO (level 0)

Iteration 1

Find direct reports (level 1)

Iteration 2

Find their reports (level 2)

Final Result

Exclude CEO, calculate differences

Code -

solution.c — C

// Recursive CTE approach (SQL string)
#include <stdio.h>
#include <string.h>

char* solution() {
    static char query[] = 
    "WITH RECURSIVE subordinate_hierarchy AS (\n"
    "    -- Base case: Find CEO\n"
    "    SELECT \n"
    "        employee_id,\n"
    "        employee_name,\n"
    "        salary as ceo_salary,\n"
    "        salary,\n"
    "        0 as hierarchy_level\n"
    "    FROM Employees \n"
    "    WHERE manager_id IS NULL\n"
    "    \n"
    "    UNION ALL\n"
    "    \n"
    "    -- Recursive case\n"
    "    SELECT \n"
    "        e.employee_id,\n"
    "        e.employee_name,\n"
    "        sh.ceo_salary,\n"
    "        e.salary,\n"
    "        sh.hierarchy_level + 1\n"
    "    FROM Employees e\n"
    "    INNER JOIN subordinate_hierarchy sh ON e.manager_id = sh.employee_id\n"
    ")\n"
    "SELECT \n"
    "    employee_id as subordinate_id,\n"
    "    employee_name as subordinate_name,\n"
    "    hierarchy_level,\n"
    "    salary - ceo_salary as salary_difference\n"
    "FROM subordinate_hierarchy\n"
    "WHERE hierarchy_level > 0\n"
    "ORDER BY hierarchy_level, subordinate_id;";
    
    return query;
}

int main() {
    printf("%s\n", solution());
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Each employee is processed exactly once in the recursion

✓ Linear Growth

Space Complexity

O(n)

Stores intermediate results for each level of recursion

⚡ Linearithmic Space

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