Just curios: can CTE's be used to make time series range queries easier / more performant?

Sqlite is the default option on Android and it's pretty common to have time series sensor data that needs to be captured, stored, and analyzed...

But sqlite isn't really meant for a time series workload.

There is also duckdb but I'm not sure about the status of the Android bindings.

> can CTE's be used to make time series range queries easier / more performant?

On their own, I'd guess likely not a lot. If a view would help, a CTE will help similarly without needing the external structure, if a correlated subquery would help, then yes similarly, especially if the pattern is repeated in the overall query.

In conjunction with other things (good indexing, materialised sequences ("numbers" tables), etc.), is guess yes.

Though you need to be much more specific about your data and the queries in question before I can do better than these vague guesses.

No. They are a query syntax, they don't change the storage or retrieval performance.

nothing really helps with potato slow Android device / storage media. CTE is not magic sauce that will make sqlite go faster.

Depending on the recursion pattern and the overhead of your sqlite driver, it can be faster to do many ID lookups then try to cram it all into one mega CTE query.

https://www.sqlite.org/np1queryprob.html

source: we have the CTE for loading page data in the notion Android app, and the network regularly beats disk on lower end Android devices using whichever query we pick.