You can just use chatgpt to rewrite sql with ctes, and extract functions. It's quite good at it, particularly gpt4.

That being said, CTEs are a really good way to write complex queries. They let you tag bits of query with meaningful names, and each thing you tag is accessible to every CTE after it so you can build up an almost imperative data flow by just doing select transforms one after another. That way you're building hard queries from the bottom up rather than the top down.

Agreed. It's so nice to be able to query intermediates to see what their output looks like and just check each step of the CTE during debugging. It was such an improvement over most subselects!