CODEWITHSUNDEEP
sqlamazon-redshift
Alex Nikitin
Alex Nikitin

Reputation: 524

SQL query with specific order of user actions

I have a table that looks like this:

user_id   user_action      timestamp
1         action #2     2016-02-01 00:02  
2         action #1     2017-10-05 15:24
3         action #3     2017-03-31 19:35
4         action #1     2017-07-09 00:24
1         action #1     2018-11-05 18:28
1         action #3     2018-02-01 13:02
2         action #2     2017-10-05 16:14
2         action #3     2017-10-05 16:34
etc

My task is to write a query where I can see user sessions, where a user performs action #1, 2, and 3 in that specific order, with time intervals between actions less than an hour. For example, user #2 have a session

2         action #1     2017-10-05 15:24
2         action #2     2017-10-05 16:14
2         action #3     2017-10-05 16:34

Sorry for lack of my own attempt, as I am really stuck and don't know, where to start. Thanks in advance!

Upvotes: 1

Views: 369

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

This can be done with window functions lead and lag which get the values from the next and previous rows respecitvely.

select distinct user_id
from (select user_id,user_action,timestamp,
      lag(user_action) over(partition by user_id order by timestamp) as prev_action,
      lead(user_action) over(partition by user_id order by timestamp) as next_action,
      datediff(minute,lag(timestamp) over(partition by user_id order by timestamp),timestamp) as time_diff_with_prev_action,
      datediff(minute,timestamp,lead(timestamp) over(partition by user_id order by timestamp)) as time_diff_with_next_action
      from tbl
     ) t
where user_action='action#2' and prev_action='action#1' and next_action='action#3'
and time_diff_with_prev_action <= 60 and time_diff_with_next_action <= 60

Upvotes: 1

Related Questions