Kevin Burkhardt

11/28/2022, 7:45 AM
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,,login 2022-11-20T18:51:10.998Z,2,,run report 2022-11-20T18:58:10.998Z,3,,logout 2022-11-20T21:41:47.376Z,4,,login 2022-11-20T21:42:41.376Z,5,,run report 2022-11-20T21:45:41.376Z,6,,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

Bolek Ziobrowski

11/28/2022, 9:07 AM
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 ( to connect two subsequent events & compute time diff like with datediff function ( :
select * 
from user_activity ua1
lt join user_activity ua2 
where datediff('h', ua2.ts, ua1.ts) >= 1;

javier ramirez

11/28/2022, 9:58 AM
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

Kevin Burkhardt

11/29/2022, 9:02 AM
Thank you, @Bolek Ziobrowski and @javier ramirez 🙂