Edit: @akira2501: SQLite comes with different tradeoffs, sometimes superior and other times less so, depending on the use case. Blanket statements without evidence are unproductive in progressing the conversation..
https://www.sqlite.org/optoverview.html
Chapter 2. Chapter 7. Chapter 8.
It should be _zero_ surprise to you that SQLite is not as powerful as other full database query engines.
But then it was quite fast.
But as with all things SQLite, the answer is generally "it's easy to predict so just improve your query" and/or "there's a plugin for that" so it tends to win in the end with a bit of care.
SQLite's planner is anything but simple. It has a beam search over different plans, basically, it approximates full NP-hard solution search process using bounded space and time.
In any case, SQLite would serve this solution, but almost certainly with a measurable level of inefficiency built in.