CTEs solve the problem to some extent only for the data-sources. You often need to have snippets, expression functions, being able to compose multiple expressions, be able to compile SQL queries that are both efficient and readable if you want to be able to cooperate with different data analysts. It's not because SQL is limited, it works the way it's designed.

Take this example: https://github.com/rakam-io/recipes/tree/master/segment/ware... It also makes use of CTEs and lateral joins but it also needs to use a templating engine (Jinja) for data models and Jsonnet for analysis models similar to LookML in order to provide that composability.

I agree with you, Looker is awesome but it's far from perfect. It does solve most of the problems in a convenient way but as the data modeling gets more and more complex and they're not going to able to iterate the product at that pace, it's the destiny of almost all the enterprise products.

Product analytics is one of these complex use-cases. You can't really find a convenient way to run funnel and retention queries in Looker, it only focuses on slicing/dicing the data with their models. Moreover, it has a high learning curve even for the enterprise and there are definitely better ways to approach this problem.

Disclaimer: We're actually working on a LookML alternative based on Jsonnet that will be a better alternative for event modeling. This is how it looks like: https://github.com/rakam-io/recipes. We recently published a VSCode extension for it: https://marketplace.visualstudio.com/items?itemName=rakam.ra...