CTEs aren't really an "extra" feature they just are a composable reusable subquery. This just adds the benefit of storing CTEs as function calls aka table valued functions (TVFs) ... also not really an "extra" feature.
The main advantage to any non SQL language is its ability to more efficiently express recursion (graph / hierarchical queries) and dynamic expressions like transposition and pivots.
The main advantage to any non SQL language is its ability to more efficiently express recursion (graph / hierarchical queries) and dynamic expressions like transposition and pivots.
You can do those in SQL it's just clunky.