Hi there -- I'm new to QuestDB and looking for guidance on how to approach the following: given a dataset of user activity like: timestamp,event_id,email,action 2022-11-20T18:44:47.376Z,1,john.doe@domain.com,login 2022-11-20T18:51:10.998Z,2,john.doe@domain.com,run report 2022-11-20T18:58:10.998Z,3,john.doe@domain.com,logout 2022-11-20T21:41:47.376Z,4,john.doe@domain.com,login 2022-11-20T21:42:41.376Z,5,john.doe@domain.com,run report 2022-11-20T21:45:41.376Z,6,john.doe@domain.com,logout I want to find the users who have been idle (or logged out) for more than a certain amount of time in a given day (e.g. > 1hr). ie. where time interval between logout/login events > 1hr (and/or time interval between any 2 successive events > 1hr). Thanks in advance for any pointers

Hi Kevin . The second condition is more generic than the first one so I'll assume the condition is : time interval between any 2 successive events > 1hr You can use lt join (https://questdb.io/docs/reference/sql/join/#lt-join) to connect two subsequent events & compute time diff like with datediff function (https://questdb.io/docs/reference/function/date-time/) :

select * 
from user_activity ua1
lt join user_activity ua2 
where datediff('h', ua2.ts, ua1.ts) >= 1;

You probably want to add an extra condition to verify

ua1.action = 'login'.

Otherwise if you are checking only for one day and the user started the session at, let’s say 23:59 the day before, you can have weird results

Thank you, @Bolek Ziobrowski and @javier ramirez 🙂