This message was deleted.

There might be a better way, but fwiw, you can definitely use something where you group by device_id and take LATEST status, maybe as a subquery, then take counts. Possibly someone else knows a faster or better way though.

@Ben Krug Thanks for the response Based on your response I was thinking of something like this

select t.device_category, t.status, count(*) from 
(select latest(device_category), latest(status), latest(device_id) from metric_table group by device_id) as temp_table t
group by t.device_category, t.status;

But the concern is that the complete table is probably scanned to fetch the latest record (pardon me if I am wrong) Is there any way I can take advantage of something like a roll-up or something like a materialized view and still store all the events?

The query would be something like:

select t.device_category, t.status, count(*) from 
(select device_category, device_id, latest(status) 
from metric_table 
WHERE __time > CURRENT_TIMESTAMP - INTERVAL '1' DAY
group by 1,2) as temp_table t
group by t.device_category, t.status;

You are correct, that without the time filter it would scan the whole table, so you would want to filter it on time, but perhaps you can use some timeframe that is safe: the max gap between events . If you have devices that do not report on a regular basis, you may need to inject an event with some frequency (daily for example) with the most recent value for that device into the stream, in order to keep the __time condition timeframe small and therefore consistent query performance.

Hi @Sergio Ferragut, there is another catch the status gets triggered only in case of any event change, so it might be possible that a sensor has been up for the last 3 months and there has been no change in its status, In that scenario the interval of day 1 will not work properly. This will be a even more complex when we have sensors which work with Hard Realtime Systems with almost 0 downtime / 1-2 events per year

Yes, that's what I mean by:

If you have devices that do not report on a regular basis, you may need to inject an event with some frequency (daily for example) with the most recent value for that device into the stream,

It would mean that you add another 365 data points for devices with lower frequency than once a day, the event would have the most recent value. So if your max timeframe for the query is 1 day, it will always find a value.

@Sergio Ferragut thanks for the response. Yes think that will work fine