I doubt sqlite will catch up soon in terms of analytics due to a few below reasons.

The SQL dialect is so much lacking that it seems intentional. They are meant to be a transactional database, not an analytics one.

Sqlite also takes pride on stability (deployed on a billion android devices). Adding 100+ analytics capabilities e.g. functions is not gonna be easy in terms of maintaining stability.

I want to be wrong though because my paid app (superintendent.app) uses Sqlite. Not supporting analytics well is the number one complaint.

Not really. There was an academic project around this but I've not seen signs that it will be integrated with SQLite core: https://simonwillison.net/2022/Sep/1/sqlite-duckdb-paper/

The key thing to consider here is trade-offs.

Analytical databases tend to be optimized for analytical queries at the expense of fast atomic read-write transactions.

SQLite is mainly used in situations where fast atomic read-write transactions are key - that's why it's used in so many mobile phone applications, for example.

It's not going to grow analytical-query-at-scale capabilities if that means negatively impacting the stuff it's really good at already.