# Aggregation: number of new users over last week

**URL:** https://discuss.elastic.co/t/aggregation-number-of-new-users-over-last-week/226477
**Category:** Elasticsearch
**Created:** [April 3, 2020, 9:30pm UTC](https://discuss.elastic.co/t/aggregation-number-of-new-users-over-last-week/226477 "2020-04-03T21:30:31Z")
**Posts on this page:** 4
**Page:** 1

<div class="post-metadata">

### Author: ![smirnov10](https://avatars.discourse-cdn.com/v4/letter/s/96bed5/32.png) [@smirnov10](https://discuss.elastic.co/u/smirnov10)
#### Post date: [April 3, 2020, 9:30pm UTC](https://discuss.elastic.co/t/aggregation-number-of-new-users-over-last-week/226477/1 "2020-04-03T21:30:31Z")

</div>

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.

---

<div class="post-metadata">

### Author: ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)
#### Post date: [April 5, 2020, 4:09pm UTC](https://discuss.elastic.co/t/aggregation-number-of-new-users-over-last-week/226477/2 "2020-04-05T16:09:42Z")

</div>

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](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html). 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.

---

<div class="post-metadata">

### Author: ![smirnov10](https://avatars.discourse-cdn.com/v4/letter/s/96bed5/32.png) [@smirnov10](https://discuss.elastic.co/u/smirnov10)
#### Post date: [April 9, 2020, 1:38pm UTC](https://discuss.elastic.co/t/aggregation-number-of-new-users-over-last-week/226477/3 "2020-04-09T13:38:16Z")

</div>

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

---

<div class="post-metadata">

### Author: ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)
#### Post date: [May 7, 2020, 1:38pm UTC](https://discuss.elastic.co/t/aggregation-number-of-new-users-over-last-week/226477/4 "2020-05-07T13:38:23Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.