It begs for examples of the ugly/verbose/complex SQL and the beautiful/succinct/simple Malloy. Looking at the samples folder did no work for me.
Think of a sales orders table. Think of all the orders in there that should be considered "invalid" for some reason or another.
It would be awesome to define some logic, one time, in one place, for "invalid orders" and then have end users be able to say "from invalid orders", "stores having no invalid orders", "customers with multiple invalid orders", "top 3 reasons orders are invalid" ...
And of course "valid orders" might just be defined as "not invalid orders"
Instead this is probably multiple views, CTEs, UDFs, and still some logic in the main query to handle NULLs or joins or whatnot.
Even little things like hierarchies - I don't want to have separate sprocs for salesByCountry, salesByRegion. salesByState,and then an umbrella dynamic sproc to figure out which one to call.
It's ironic because SQL is such a human friendly declarative language that it has such poor ability to create meaningful shorthand expressions.
I've written SQL for nearly 30 years, I love it, but natural language transpilers have shown me some of its limits as an expressive tool.
It's a separate system, which has downsides. As an upside, by being a component of your system, the integrations with monitoring, alerting, quality checks, and dashboards is usually pretty easy.
It should all be in one SQL-like DSL with a natural language transpiler on top.
you can use CTEs but only with one query
Don't e.g. Postgres temporary views cover this case?
"Temporary views are automatically dropped at the end of the current session."
I expect vendor specific stuff called something along the lines of temporary view would do this, yes
(Edited for clarity.)