Cheat Sheet logoCheat Sheet Today
SQL Window Functions and CTEs/Recursive CTE

Traverse hierarchical or graph-like data.

Back to sheetBrowse commands
Sign in to save
Section: Common Table Expressions

Recursive CTE

sql
sql
WITH RECURSIVE org_tree AS (
  SELECT employee_id, manager_id, name, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.employee_id, e.manager_id, e.name, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON ot.employee_id = e.manager_id
)
SELECT *
FROM org_tree
ORDER BY depth, employee_id;
Explanation

Recursive CTEs are powerful for trees, nested categories, and parent-child traversal.

View in sheet →

Learn the surrounding workflow

Compare similar commands or jump into common fixes when this command is part of a bigger troubleshooting path.

Compare commandsView fixes

Related commands

Same sheet · prioritizing Common Table Expressions
Basic CTE
Create a named subquery for readability and reuse.
OpenIn sheetsqlsame section
Multiple CTEs
Chain multiple named steps in one query.
OpenIn sheetsqlsame section
Assign row numbers
Number rows within an ordered partition.
OpenIn sheetsql
Use LAG
Read a value from the previous row in the same partition.
OpenIn sheetsql
Bucket rows with NTILE
Divide ordered rows into a fixed number of groups.
OpenIn sheetsql
Rank rows
Assign ordered rankings with or without gaps.
OpenIn sheetsql