0Pricing
SQL Interview Prep · Lesson

Traversing an Org Chart

Walking an employee-manager hierarchy to any depth.

Traversing an Org Chart is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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)
);

Walking Downward From a Manager

To list all reports under a chosen manager, the anchor selects that manager (or their direct reports), and the recursive member follows manager_id downward.

Here we start from Ben (id 2) and collect everyone beneath him.

WITH RECURSIVE subtree AS (
    SELECT id, name, manager_id, 1 AS depth
    FROM employees WHERE id = 2
    UNION ALL
    SELECT e.id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    JOIN subtree s ON e.manager_id = s.id
)
SELECT name, depth FROM subtree ORDER BY depth;

Reading the Output

The query above returns Ben at depth 1, Dan at depth 2, Eve at depth 3. The anchor seeded Ben; iteration one found Dan (whose manager is Ben); iteration two found Eve (whose manager is Dan); iteration three found nobody, so recursion stopped.

If the interviewer asks "how many levels deep does Eve sit below Ben?", the depth column answers it directly: 3 minus 1 equals 2 levels.

Walking Upward to the CEO

The reverse question is just as common: "Show Eve's full chain of command up to the CEO." Flip the join direction — the recursive member now follows the current row's manager_id up to the parent.

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 1 AS lvl
    FROM employees WHERE id = 5
    UNION ALL
    SELECT e.id, e.name, e.manager_id, c.lvl + 1
    FROM employees e
    JOIN chain c ON e.id = c.manager_id
)
SELECT name, lvl FROM chain ORDER BY lvl;

Down vs Up: The Join Flips

The only structural difference between walking down and walking up is the join condition:

  • Down (find reports): e.manager_id = cte.id — match employees whose manager is a row we already have.
  • Up (find managers): e.id = cte.manager_id — match the employee whose id is our current row's manager.

Being able to articulate this flip cleanly impresses interviewers.

Building an Indented Tree

A polished answer formats the output as an indented tree using depth to repeat spaces. This shows you can present hierarchy results, not just compute them.

WITH RECURSIVE org AS (
    SELECT id, name, 1 AS depth
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, o.depth + 1
    FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT REPEAT('  ', depth - 1) || name AS tree
FROM org
ORDER BY depth;

Accumulating the Path

To show the full route from CEO to each person, carry a path string. This is the same technique from the previous lesson, applied to the org chart.

WITH RECURSIVE org AS (
    SELECT id, name, CAST(name AS VARCHAR(500)) AS path
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, o.path || ' / ' || e.name
    FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT name, path FROM org ORDER BY path;

Counting Reports Per Manager

A frequent follow-up: "How many people, directly or indirectly, report to each manager?" Use the recursive subtree per manager, then aggregate. A common pattern is to run the recursion once per root and GROUP BY the seed manager.

Here we count all indirect reports beneath Ada (the CEO) by walking the whole tree and counting rows below the root.

WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id, o.depth + 1
    FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT COUNT(*) - 1 AS total_reports FROM org;

Common Mistakes

Watch for these traps interviewers set:

  • Wrong join direction — using e.manager_id = cte.id when you meant to go up returns the wrong set.
  • Forgetting the anchor filter — omit WHERE id = X and you seed every row, returning the entire forest.
  • Off-by-one depth — decide whether the seed is depth 0 or 1 and stay consistent.

Why Not Just Self-Join?

A self-join can fetch a fixed number of levels: one join for direct reports, two for grand-reports, and so on. But you must know the depth in advance and write a join per level.

A recursive CTE handles arbitrary, unknown depth in one query. When an interviewer says "the hierarchy can be any number of levels," that rules out plain self-joins and signals recursion.

Quick Check

Make sure you can flip the traversal direction.

Recap

Org-chart traversal is the recursive skeleton applied to a self-referential table:

  • Down: seed a manager, join e.manager_id = cte.id.
  • Up: seed an employee, join e.id = cte.manager_id.
  • Carry depth for indentation and path for the full chain.
  • Recursion handles any unknown depth, where a self-join cannot.

Next: using recursion to generate number and date series.

Frequently asked questions

Is the “Traversing an Org Chart” lesson free?

Yes — the full text of “Traversing an Org Chart” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Traversing an Org Chart”?

Walking an employee-manager hierarchy to any depth. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Traversing an Org Chart” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

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