recursive cte

Hierarchies (org charts, bill-of-materials, category trees) call for recursion, and a self-join reaches only one level down. The signal is the recursive CTE with its anchor plus recursive members, and understanding how it terminates.

An org-chart recursion walks a tree, but a general graph has multiple paths and back-edges. The signal is building up the visited path to prune cycles and ranking paths by cost to land the shortest one.