If you will kindly send your problem query to the SQLite
developers, we will do what we can to improve the query planner
to make it run faster.
I may have misremembered since I did go a bit wild on this query (done while learning about recursive queries), but pulling the subquery into a CTE doesn't affect the query plan.