I have a pubsub channel where an event is fired every time a user logs in, and I want to be able to query the unique users in a date range.
Solutions I thought:
- Put the data in bigquery, and then use
APPROX_COUNT_DISTINCT, but it's too expensive
- Same as above, plus a cache. Past data doesn't change, so it's a good approach, but still very expensive because I would need to import the pubsub channel in bigquery
- Precompute daily uniques, and then do something very rough like
I was also thinking about storing a 64 bytes bloom filter and a counter per day, then merge the filters in range and do some estimation on the count, but I couldn't find any paper on it.
Any better idea?
If it can be helpful we're speaking about 2/3 gb of data per day, around 6 months and growing.