It's unusual and not a commonly needed tool in SQL. I always need a quick refresher on how to write these after a longer break

Not commonly needed presumably only because we've grown up to understand that SQL is not a language where you can easily express rescursively hierarchical relationships, such as the in the go-to example where you have a table of employees with a 'reports-to' field. In classical SQL there was no way to write a simple query that will resolve 'who is reporting to who' recursively with an arbitrary depth. Recursive CTEs can do that.