# Getting latest data per user\_id in time series data without latest transforms? **URL:** \ **Category:** Elasticsearch\ **Created:** [June 30, 2023, 4:06pm UTC](https://discuss.elastic.co/t/getting-latest-data-per-user-id-in-time-series-data-without-latest-transforms/337329 "2023-06-30T16:06:20Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![MaterializedView](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/materializedview/32/122903_2.png) [@MaterializedView](https://discuss.elastic.co/u/MaterializedView)\ **Post date:** [June 30, 2023, 4:06pm UTC](https://discuss.elastic.co/t/getting-latest-data-per-user-id-in-time-series-data-without-latest-transforms/337329/1 "2023-06-30T16:06:20Z")
I have a users index. Users have various status "New", "Waiting", "Completed". A status can go from "Completed" to "New" again. So in time series it would look something like user\_id, status, timestamp 1 NEW 2023-06-30 5:00 1 WAITING 2023-06-30 6:00 1 COMPLETED 2023-06-30 7:00 1 NEW 2023-06-30 8:00 2 NEW 2023-06-30 5:00 2 WAITING 2023-06-30 6:00 2 COMPLETED 2023-06-30 7:00 I have millions of users. I would like to get number of users who are correctly in NEW state. The result should be 1 but with pivot aggregations I get count = 2 because userId:1 was COMPLETED at some point but then back to "NEW". Latest transforms are too expensive usage wise, so we want to run these queries on demand. Is collapse or pipeline aggregations possible? Could someone help me here. ---
**Author:** ![Alexis\_Roberson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alexis_roberson/32/113233_2.png) [@Alexis\_Roberson](https://discuss.elastic.co/u/Alexis_Roberson)\ **Post date:** [June 30, 2023, 6:12pm UTC](https://discuss.elastic.co/t/getting-latest-data-per-user-id-in-time-series-data-without-latest-transforms/337329/2 "2023-06-30T18:12:07Z")
Can you ask your question in a different way? You mentioned you have an existing index, do you also have time series data? And are you saying the user index is being updated so that the status periodically movies from "Completed" to "New" value? ---
**Author:** ![MaterializedView](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/materializedview/32/122903_2.png) [@MaterializedView](https://discuss.elastic.co/u/MaterializedView)\ **Post date:** [June 30, 2023, 6:21pm UTC](https://discuss.elastic.co/t/getting-latest-data-per-user-id-in-time-series-data-without-latest-transforms/337329/3 "2023-06-30T18:21:00Z")
Yes I have a users\_time\_series index. The user status goes from NEW to WAITING to COMPLETED. In some cases it can go to NEW \> WAITING \> COMPLETED \> NEW Since its time series, we just append to the index. I want to get the count of users who are currently in COMPLETED status. I can do this via latest transforms but I was told, the usage isn't that high so we must do it without transforms. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 28, 2023, 6:21pm UTC](https://discuss.elastic.co/t/getting-latest-data-per-user-id-in-time-series-data-without-latest-transforms/337329/4 "2023-07-28T18:21:41Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.