HardCTEs

Manager-Employee Organizational Hierarchy Depth

Frequently Asked By:GoogleMicrosoftAmazon

Interactive In-Browser Workspace

Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.

Solve in Workbench

Problem Statement

Given an employee table with employee_id, name, and manager_id (where the CEO has manager_id IS NULL), write a recursive SQL query to output each employee, their direct manager name, and their hierarchy depth level starting from the CEO at Level 1.

Why Technical Interviewers Ask This

Recursive Common Table Expressions (WITH RECURSIVE) are the definitive test of whether an engineer understands graph traversal, hierarchical data modeling, and terminating base conditions directly within database engines.

Common Pitfalls & Interview Traps

Infinite recursion caused by cyclical manager loops or forgetting the WHERE termination condition.

Using UNION instead of UNION ALL in the recursive CTE step (UNION requires an expensive deduplication sort on every iteration).

Incorrect anchor term selection (starting from bottom up instead of top down).

Canonical Modern SQL Solution

PostgreSQL 16 / ANSI SQL

WITH RECURSIVE OrgHierarchy AS (
  -- 1. Base / Anchor member: Top-level executives (manager_id IS NULL)
  SELECT
    employee_id,
    name AS employee_name,
    manager_id,
    1 AS hierarchy_level,
    CAST(name AS TEXT) AS hierarchy_path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 2. Recursive member: Join subordinates to their managers
  SELECT
    e.employee_id,
    e.name AS employee_name,
    e.manager_id,
    o.hierarchy_level + 1,
    o.hierarchy_path || ' -> ' || e.name
  FROM employees e
  INNER JOIN OrgHierarchy o ON e.manager_id = o.employee_id
)
SELECT
  employee_id,
  employee_name,
  hierarchy_level,
  hierarchy_path
FROM OrgHierarchy
ORDER BY hierarchy_level ASC, employee_name ASC;

Approach Breakdown

The anchor term selects the root of the tree (manager_id IS NULL) and initializes hierarchy_level = 1. The recursive term iteratively joins employees whose manager_id matches the previously resolved hierarchy layer, incrementing the level counter until no more subordinates are found.

Edge Cases to Verbally Clarify with Your Interviewer

Multiple co-CEOs or board members with NULL manager_id.

Employees who are individual contributors with no direct reports.

Deeply nested chains (e.g. 10+ levels of management).

Frequently Asked Follow-Up Questions

What stops a recursive CTE from executing infinitely?

The recursion automatically terminates when the recursive SELECT query returns zero rows. If data contains cyclic references (e.g., A manages B and B manages A), modern Postgres allows cycle detection or setting a safety ceiling like WHERE hierarchy_level < 50.