TL;DR: SQLite was doing OLAP work it was never built for (and it was okay at it, I must add), and the perf. ceiling moves two orders of magnitude when you use something that's more fit for the purpose (DuckDB in this case).

TLDRTL;DR: If everything you do is column-store territory, use a column-store.