How to get user sessions?

Question

I'm working with redash and I need to get user rows where for each row delta between date field is less than hour.

In more details: I need a session, user activity where it has some actions where end of the session defined by last action + 1 hour.

users row is <id, action, date>

user_id     page    happened_at     
179,233 rooms.view.step.content  2017-03-01 09:24
179,233 rooms.view.step.content  2017-03-01 09:01
179,233 rooms.student-showcase   2017-03-01 12:02

datediff should help there, but it is not available at redash - redshift.

I'm looking for alternatives. Anyone has thoughts there?

Answer 1

Please try this. You may even choose dateadd instead.

select id, action, date 
from users u1 
where exists 
      ( select 1 from users u2 
        where u1.id = u2.id 
        and u2.happened_at < (u1.happened_at + interval '1 hour') 
        and u2.happened_at > u1.happened_at )
union  
select id, action, date 
from users u1 
where exists 
      ( select 1 from users u2 
        where u1.id = u2.id 
        and u2.happened_at > (u1.happened_at + interval '1 hour') 
        and u2.happened_at < u1.happened_at )

By the way, redshift has datediff. Not sure why is it not supported in redash.