Those are two very different types of workloads (OLTP vs OLAP), and historically a lot of the database products specialized in only one of them. Relational databases and SQL just happened to be extraordinarily resilient at handling both types of workloads.

Cloud Firestore is best as an OLTP database as you’ve pointed out. You can try to run large analytic queries on it using one of the server SDKs, but it will not be very efficient or cost-effective.

In the GCP ecosystem Bigquery is the most prominent solution for OLAP workloads. It’s great for dealing with massive datasets, and supports SQL. Firestore users can export their data into Bigquery to run their OLAP queries. See the documentation for more information: https://cloud.google.com/bigquery/docs/loading-data-cloud-firestore