CPMagical CP Library

Problem solution · SQL

Analyze Organization Hierarchy

Analyze Organization Hierarchy: a SQL solution using depth-first search. Learn the idea, check the complexity, and read the full code, with credit to walkccc LeetCode Solutions.

Technique
Depth-first search
Source
walkccc LeetCode Solutions
Length
68 lines
Start with the idea.

Try the problem first. If you get stuck, read the approach below, then write your own solution. The full code is at the bottom.

Approach

Depth-first search

For Analyze Organization Hierarchy, the implementation follows one branch at a time, making it suitable for components, trees, backtracking, or dependency exploration.

  1. Define the state carried into one recursive or stack frame.
  2. Mark or choose the current state before exploring children.
  3. Combine child results or undo the choice when the branch finishes.

Code notes

  • 68 lines of SQL from the credited upstream file 3482.sql.
  • The implementation keeps its working state in language-native values and containers.
  • No explicit loop blocks detected.

Complexity

Count unique states for graph traversal; for backtracking, count the branching factor and maximum depth.

Check the problem constraints before deciding whether this complexity will pass.

Source

Code and credit

This code comes from walkccc LeetCode Solutions by P.-Y. Chen (walkccc) and is used under the MIT licence.

Open problem ↗Original source ↗
Full codeAnalyze Organization Hierarchy · SQLSQL
Use this to learn the idea, then write your own version.
WITH RECURSIVE  EmployeeHierarchy AS (    -- Base case: direct reports to CEO    SELECT      employee_id,      employee_name,      manager_id,      salary,      1 AS level    FROM Employees    WHERE manager_id IS NULL    UNION ALL    -- Recursive case: reports of reports    SELECT      Employees.employee_id,      Employees.employee_name,      Employees.manager_id,      Employees.salary,      EmployeeHierarchy.level + 1    FROM Employees    INNER JOIN EmployeeHierarchy      ON (Employees.manager_id = EmployeeHierarchy.employee_id)  ),  -- Calculate team size and budget for each employee  TeamSizeAndBudget AS (    WITH RECURSIVE      -- Get all subordinates (direct and indirect)      Subordinates AS (        -- Base case: direct reports        SELECT          manager_id,          employee_id,          salary        FROM Employees        WHERE manager_id IS NOT NULL        UNION ALL        -- Recursive case: indirect reports        SELECT          Subordinates.manager_id,          Employees.employee_id,          Employees.salary        FROM Employees        INNER JOIN Subordinates          ON (Employees.manager_id = Subordinates.employee_id)      )    SELECT      Employees.employee_id,      COUNT(DISTINCT Subordinates.employee_id) AS team_size,      IFNULL(SUM(Subordinates.salary), 0) + Employees.salary AS total_budget    FROM Employees    LEFT JOIN Subordinates      ON (Employees.employee_id = Subordinates.manager_id)    GROUP BY Employees.employee_id, Employees.salary  )SELECT  EmployeeHierarchy.employee_id,  EmployeeHierarchy.employee_name,  EmployeeHierarchy.level,  IFNULL(TeamSizeAndBudget.team_size, 0) AS team_size,  IFNULL(TeamSizeAndBudget.total_budget, EmployeeHierarchy.salary) AS budgetFROM EmployeeHierarchyLEFT JOIN TeamSizeAndBudget  USING (employee_id)ORDER BY  EmployeeHierarchy.level,  TeamSizeAndBudget.total_budget DESC,  EmployeeHierarchy.employee_name;

Keep practising

Did this explanation save you time? I'm a Grade 11 student building this free library to make difficult algorithms easier to understand.

Buy me a coffee ↗