Cheat Sheet logoCheat Sheet Today
SQLite Joins, CTEs, and Window Functions Cheatsheet/Use a recursive CTE

Walk a hierarchy such as categories or folders.

Back to sheetBrowse commands
Sign in to save
Section: CTEs and window functions

Use a recursive CTE

sql
sql
WITH RECURSIVE category_tree(id, name, parent_id, depth) AS (
  SELECT id, name, parent_id, 0 FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;
Explanation

Recursive CTEs are the go-to pattern for tree traversal in SQLite.

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 CTEs and window functions
Use a CTE to stage a subquery
Name an intermediate result set.
OpenIn sheetsqlsame section
Assign row numbers
Rank rows within an ordered result.
OpenIn sheetsqlsame section
Compute a running total
Use SUM() OVER for cumulative metrics.
OpenIn sheetsqlsame section
Rank rows by score
Assign rank values with ties.
OpenIn sheetsqlsame section
Use an INNER JOIN
Return rows that match in both tables.
OpenIn sheetsql1 tag match
Use a LEFT JOIN
Keep parent rows even if the child is missing.
OpenIn sheetsql1 tag match