0PricingLogin▼
SQL Interview Prep · Lesson

Traversing an Org Chart

Walking an employee-manager hierarchy to any depth.

The Org-Chart Question

"Given an employees table with id, name and manager_id, list everyone under a given manager, to any depth." This is one of the most common recursive-CTE interview prompts.

The table is self-referential: manager_id points back to another row's id. In this lesson you will walk it both downward (reports) and upward (chain of command).

The Sample Table

Picture this data. The CEO has a NULL manager. Everyone else reports up the chain.

  • 1 Ada (manager NULL)
  • 2 Ben (manager 1)
  • 3 Cleo (manager 1)
  • 4 Dan (manager 2)
  • 5 Eve (manager 4)

So the depth is: Ada → Ben → Dan → Eve. Keep this in mind as we walk it.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT REFERENCES employees(id)
);

All lessons in this course

  1. Anchor and Recursive Members
  2. Traversing an Org Chart
  3. Generating Number and Date Series
  4. Avoiding Infinite Recursion
← Back to SQL Interview Prep