recursive cte

Hierarchies (org charts, bill-of-materials, category trees) need recursion, and a self-join only goes one level deep. The signal is the recursive CTE with its anchor plus recursive members, and knowing how it terminates.

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