Used judiciously, a macro system in front of your SQL is often a good approach here. You gain the "linguistically common subtable" capabilities of a CTE, the ability to name constants, and the ability to name/parameterize common sub-expressions, even in databases not supporting CTEs. With LSP support being what it is, you can even whip up editor integration for your new language in a day or less.

Compared to CTEs (assuming you're not using recursion and don't _want_ the materialization), that extra syntactic sugar helps with readability, and the "macro" nature of the thing ensures you don't have any slowdowns from running the thing due to a different query plan.

Downsides include the ability for juniors to turn your SQL into an awful mess, the fact that you'll still occasionally _want_ materialized CTEs for performance and have to write them anyway, plus if you implement it wrong you'll have runtime overhead and very few introspection capabilities (the simplest version I've seen that's decent to work with is creating SQL files as artifacts from the templates using the build system, obviously depending on how much of a rube goldberg your particular builds are).