Agent reporting chain

HR is reorganising the help desk and wants every agent’s full line of command.

In agents, manager_id points at the agent’s manager. Agents with a NULL manager_id are at the top. Today every chain is short, but the org is about to get deeper, so your query must handle chains of any length. You can assume there are no cycles.

Return one row per agent:

column	type	meaning
agent_id	integer	the agent
name	text	the agent’s name
depth	integer	0 for a top manager, 1 for someone reporting to one, and so on
top_manager_id	integer	the agent_id at the top of this agent’s chain (themselves if depth is 0)
chain	text	agent ids from the top down to this agent, joined with ' > ', e.g. '1 > 4'

A top manager’s chain is just their own id, like '1'.

Row order doesn’t matter.

Tables: agents.

Approach

Start from the top managers and walk down the tree one level per iteration, carrying the depth, top manager and chain along.

WITH RECURSIVE org AS (
  -- anchor: the top of every chain
  SELECT agent_id,
         name,
         0 AS depth,
         agent_id AS top_manager_id,
         CAST(agent_id AS VARCHAR) AS chain
  FROM agents
  WHERE manager_id IS NULL

  UNION ALL

  -- step: everyone who reports to someone already found
  SELECT a.agent_id,
         a.name,
         o.depth + 1,
         o.top_manager_id,
         o.chain || ' > ' || CAST(a.agent_id AS VARCHAR)
  FROM agents a
  JOIN org o ON a.manager_id = o.agent_id
)
SELECT agent_id, name, depth, top_manager_id, chain
FROM org;

Why it works

The anchor produces level 0. Each pass of the recursive step joins agents against only the rows found in the previous pass, so it finds exactly the next level down. When a pass finds no one, the recursion stops. The result is the union of every level.

Going top-down means everything you need is already known when you reach a row. top_manager_id is copied unchanged from the manager, and depth and chain extend the manager’s values by one step. Each agent has one manager, so each agent is reached by exactly one path and appears once.

Without the “no cycles” guarantee, a loop (A manages B, B manages A) would recurse forever. A guard like WHERE o.depth < 50 or checking that the id isn’t already in the chain would stop it.

Under the hood

DuckDB runs this as a loop over a small working table. Pass 1 holds the anchor rows. Each pass hash-joins agents against the working table, appends the result to the output, and makes it the new working table. It stops when a pass produces zero rows. The number of passes is the tree’s height plus one, and each pass only touches one level, so the whole walk costs about one join over agents per level.