I have page view event data that is currently stored in Google Firestore that gets loaded automatically into a BigQuery dataset. I am using this data to show total page counts and "trending" page counts (total views within last 48 hours).
I need to feed the total and trending page counts (grouped by page) to a database to use in the front-end website. I have written BigQuery SQL to query the total counts, but I worry that these queries will become inefficient over time.
It seems that I should somehow pre-aggregate the counts in a table as the events come in (streaming).
What is the most efficient way to aggregate time series data like this?
Consider materialized views. They require minimum maintenance.