Recursive CTE with cycle detection — how do you handle circular manager references and produce a search order?

Q: Recursive CTE with cycle detection — how do you handle circular manager references and produce a search order?

**Target Audience:** Developers who can write a recursive CTE and now need it to survive bad data; candidates asked the "what if the hierarchy has a loop?" follow-up. **Difficulty:** Hard **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. The `CYCLE` and `SEARCH` clauses are PostgreSQL 14 and later. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error below was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First You are following signs to a museum. Each sign points to the next one. After a while you notice the café on the corner: you have passed it before. Someone has turned a sign around, and you are walking in a circle. Without noticing the café you would walk forever.

The problem

Question presented to candidate: "A bad data import left three managers reporting to each other in a circle, and now our org-chart query never finishes. How do you make a recursive CTE safe against cycles, how do you find the people caught in the loop, and how do you control whether the result comes out depth-first or breadth-first?"

What a strong answer should cover:

Why it loops: with UNION ALL every iteration finds rows again, the working table never becomes empty, and PostgreSQL and SQLite have no built-in recursion limit
Portable cycle detection: carry the visited path (a delimited string, or an array in PostgreSQL), flag a row whose id is already in the path, and stop expanding flagged rows
PostgreSQL 14+ CYCLE id SET is_cycle USING path, which generates that bookkeeping for you, and SEARCH DEPTH FIRST / SEARCH BREADTH FIRST BY ... SET ordercol for the output order
Cheaper partial defences: UNION (stops only when whole rows repeat) and a depth guard (bounds the work but returns repeated rows)
A data-quality query that lists the employees who are on a loop, and preventing it at write time
Search order: breadth-first = order by depth; depth-first = order by the path; SQLite can also pick the queue order with ORDER BY inside the recursive member

Clarifying questions expected:

Do you want the query to tolerate the bad rows and still return everything reachable, or to report them?
Which order does the consumer need: level by level, or each manager followed by their whole team?

Code / implementation expected: Yes — a recursive walk with path-based cycle detection, plus the PostgreSQL CYCLE / SEARCH clauses or an equivalent ordering.

Schema used by the examples:

recursive-cte
cycle-detection
postgres

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who can write a recursive CTE and now need it to survive bad data; candidates asked the "what if the hierarchy has a loop?" follow-up. Difficulty: Hard Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. The CYCLE and SEARCH clauses are PostgreSQL 14 and later.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error below was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

You are following signs to a museum. Each sign points to the next one. After a while you notice the café on the corner: you have passed it before. Someone has turne

The solution is hidden so you can try first

14 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLPortable cycle detection: carry the visited path and stop at a repeated id

SQLData-quality check: which employees sit on a management loop?

PostgreSQL 14+: the CYCLE clause does the bookkeeping

WITH RECURSIVE walk(id, name, depth) AS (
  SELECT id, name, 0 FROM employees WHERE id = 7
  UNION ALL
  SELECT e.id, e.name, w.depth + 1
  FROM employees AS e
  JOIN walk AS w ON e.manager_id = w.id
) CYCLE id SET is_cycle USING path
SELECT name, depth, is_cycle, path
FROM walk
ORDER BY depth, name;

PostgreSQL 14+: SEARCH DEPTH FIRST for a tree-ordered result

WITH RECURSIVE tree(id, name, depth) AS (
  SELECT id, name, 0 FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, t.depth + 1
  FROM employees AS e
  JOIN tree AS t ON e.manager_id = t.id
) SEARCH DEPTH FIRST BY name SET dfs_order
SELECT name, depth, dfs_order
FROM tree
ORDER BY dfs_order;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.