I like SQLite a lot, but I found SQLite's recursive CTE implementation to be somewhat limited: https://dercuano.github.io/notes/why-html-is-not-a-programmi...
Has that improved?
(sorry about the embarrassing arrogant pedant attitude in that note, but it's too late to fix it now)
The 3.35 release added more good stuff for CTEs, as well as a RETURNING clause and a much more flexible UPSERT.
I think it's still the case that SQLite doesn't permit the use of recursive CTEs in subqueries.
I have make a copy of the Collatz Conjecture CTE that you linked to and was going to see if I could get it to work in SQLite for the next release cycle. I don't (yet) see any reason why it shouldn't work to have the recursive reference down inside a subquery, as long as there is only one recursive reference. No promises. We'll see how it goes.
Have you thought about offering an interface with an intentionally-Turing-incomplete subset of SQL in SQLite, for making queries that can be guaranteed to terminate? Because it doesn't sit right with me that SQL is Turing-complete now. https://news.ycombinator.com/item?id=26529789 goes into more detail on decidable query languages that can still accommodate transitive closure.