Some data is hierarchical or chained: an org chart of managers, a tournament bracket, a sequence of dates, a tree of categories. Ordinary queries cannot traverse an unknown number of levels, because you would need to know in advance how many joins to write. A recursive CTE solves this by repeatedly applying a query to its own growing result until no new rows appear.
A recursive CTE has a distinctive three-part shape: an anchor member that produces the starting rows, a recursive member that derives new rows from the previous iteration, and a UNION that combines them. The engine runs the recursive member again and again, each time feeding the previous output back in, until an iteration adds nothing further, at which point the recursion stops.
Recursive CTEs are the standard SQL tool for hierarchies and generated sequences, problems that are awkward or impossible to express otherwise. They require care, because a missing termination condition causes infinite recursion, but once understood they elegantly handle org charts, bill-of-materials trees, captaincy chains, and gap-free date series within a single, self-contained query.