Aggregation: number of new users over last week

[smirnov10]

Hi,
I read/tried plenty of aggregation examples but I still cannot come up with the complete solution

I need to display new users over the week (day/month) trend chart. In SQL it may look like this:

select count(email)
from hits
group by email
having sum(case when logged_at < '2020-02-01' then 1 else 0 end) = 0

-- '2020-02-01' reflects last week and will be eventually a param

How to do the same by aggregation and display in Grafana?

Thanks.

[Christian_Dahlqvist]

Given that a user may have first logged in a long time ago and only now reappears doing this correctly through an aggregation would be computationally expensive and potentially slow. A better way might be to create an entity-centric index that hold a document per user through transforms. These documents can hold the creation date for each user which makes creating the aggregation you describe very easy and also very fast as a much smaller number of documents need to be considered and aggregated over.

[smirnov10]

Thanks @Christian_Dahlqvist. I suspected I needed a materialized view for that.