Question 1

Recursive CTE for an org chart — how do you list all reports (direct + indirect) of a manager?

Accepted Answer

**Target Audience:** Developers who know JOINs and have seen a basic recursive CTE; the applied interview version of "walk a hierarchy".
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15; every query below was run on both and returned the same rows.

> **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 interview

Question 2

How do recursive CTEs work?

Accepted Answer

**Target Audience:** Anyone who can write a JOIN and a basic CTE, meeting `WITH RECURSIVE` for the first time; and candidates who want to explain the execution model precisely.
**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. MySQL and SQL Server facts are cited, not run.

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:`

Question 3

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

Accepted Answer

**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 t

SQLDECODED.

Medium

Recursive CTE for an org chart — how do you list all reports (direct + indirect) of a manager?

Hard

How do recursive CTEs work?

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