Employee Hierarchy Two Levels — Recursive CTEs in SQL

The problem

Helix Systems' executive reporting system retrieves the top two levels of the company hierarchy — the CEO and every employee who reports directly to the CEO.

Write a query to return the ID, name, and depth level for every employee in those top two levels.

Assumptions:

The employees table has one row per employee with an id, a name, and a manager_id.
The CEO has a missing manager_id. Every other employee has a manager_id referencing another employee's id.
Only the CEO and that person's direct reports should appear. The CEO carries depth 1; direct reports carry depth 2. Indirect reports do not appear.

Output:

One row per qualifying employee, with columns id, name, and depth.

Schema · hr 4 tables

departments

id integer

name text

location text

budget numeric

salaries

id integer

employee_id integer

amount numeric

effective_date date

end_date? date

employees

id integer

name text

email text

department_id integer

manager_id? integer

hire_date date

title text

is_active boolean

job_history

id integer

employee_id integer

title text

department_id integer

start_date date

end_date? date

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

WITH RECURSIVE
  hierarchy AS (
    SELECT
      id,
      name,
      1 AS depth
    FROM
      employees
    WHERE
      manager_id IS NULL
    UNION ALL
    SELECT
      e.id,
      e.name,
      h.depth + 1
    FROM
      employees e
      JOIN hierarchy h ON e.manager_id = h.id
    WHERE
      h.depth < 2
  )
SELECT
  id,
  name,
  depth
FROM
  hierarchy

The shape

The anchor seeds the CTE with the CEO, and the recursive member adds the CEO's direct reports — but only those — by stopping the recursion when the previous pass's row sits at depth 2 or deeper. WHERE h.depth < 2 is the depth limit that caps the traversal at two levels.

Clause by clause

The anchor seeds the CEO at depth 1:

SELECT id, name, 1 AS depth
FROM employees
WHERE manager_id IS NULL

The CEO is the employee with no recorded manager_id. That row enters hierarchy carrying depth = 1.

The recursive member adds direct reports and then stops:

UNION ALL
SELECT e.id, e.name, h.depth + 1
FROM employees e
JOIN hierarchy h ON e.manager_id = h.id
WHERE h.depth < 2

The join e.manager_id = h.id links every employee to the row already in hierarchy who is their manager. The filter h.depth < 2 keeps only the rows where the source row sits at depth 1 — that is, where the manager is the CEO. The pass therefore emits Sarah Chen's nine direct reports, each stamped depth = 2. The next pass would need a row in hierarchy whose depth is still below 2, but every newly added row is at 2, so no further expansion happens and the recursion terminates.

The final SELECT returns both levels:

SELECT id, name, depth
FROM hierarchy

The CEO plus the nine direct reports — ten rows in total.

The trap

The depth filter belongs on the source row (h.depth), not on the row being produced (h.depth + 1). WHERE h.depth < 2 says "only recurse from rows that sit above depth 2," which produces rows at depth 2 and stops. Writing WHERE h.depth + 1 <= 2 would produce the same result, but WHERE h.depth < 2 reads cleaner and matches the standard depth-limit pattern: cap on the value you have, not on the value you are about to compute.

You practiced a depth-bounded WITH RECURSIVE — adding WHERE depth < N to the recursive step caps traversal at a known level rather than following the hierarchy to its leaves.

Return the ID, name, and depth level for every employee in those top two levels

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.