psrpsrpsr psrpsrpsr - 10 days ago 6
SQL Question

How can I return rows that meet a specific sequence of events?

I am trying to pull records for UserIDs that meet a certain sequence of events. If a user has a JOIN, then a subsequent CANCEL, and then a subsequent JOIN, I want to return them in the result set. I need to run this query for one day at a time, or several days at a time, as needed.

The table below shows examples of UserIDs that meet and do not meet the sequence.

+--------+--------+---------------------+------------+------------------+
| rownum | UserID | Timestamp | ActionType | Return in query? |
+--------+--------+---------------------+------------+------------------+
| 1 | 12345 | 2016-11-01 08:25:39 | JOIN | yes |
| 2 | 12345 | 2016-11-01 08:27:00 | NULL | yes |
| 3 | 12345 | 2016-11-01 08:28:20 | DOWNGRADE | yes |
| 4 | 12345 | 2016-11-01 08:31:34 | NULL | yes |
| 5 | 12345 | 2016-11-01 08:32:44 | CANCEL | yes |
| 6 | 12345 | 2016-11-01 08:45:51 | NULL | yes |
| 7 | 12345 | 2016-11-01 08:50:57 | JOIN | yes |
| 1 | 9876 | 2016-11-01 16:05:42 | JOIN | yes |
| 2 | 9876 | 2016-11-01 16:07:33 | CANCEL | yes |
| 3 | 9876 | 2016-11-01 16:09:09 | JOIN | yes |
| 1 | 56565 | 2016-11-01 18:15:16 | JOIN | no |
| 2 | 56565 | 2016-11-01 19:22:25 | CANCEL | no |
| 3 | 56565 | 2016-11-01 20:05:05 | CANCEL | no |
| 1 | 34343 | 2016-11-01 05:32:56 | JOIN | no |
+--------+--------+---------------------+------------+------------------+


I have read up on gaps and islands, and looked at all sorts of complicated forum posts that dance around what I'm trying to achieve.

Currently, all I'm able to do is look at one day's worth of records, with no constraint on the sequence logic that I need:

SELECT
ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY tmsmp) rownum
,UserID
,tmstmp
,ActionType
FROM
t
INNER JOIN (
SELECT UserID
FROM t
WHERE tmstmp BETWEEN '2016-11-20 00:00:01' AND '2016-11-20 11:59:59'
GROUP BY UserID
HAVING COUNT(*) >= 2
) AS sub ON t1.UserID = sub.UserID


Thank you for your input!

Answer

You can use LEAD() :

SELECT * FROM (
    SELECT t.* ,
           LAG(t.ActionType,1) OVER(PARTITION BY t.userid ORDER BY t.timestamp) AS LAST_ACTION,
           LAG(t.ActionType,2) OVER(PARTITION BY t.userid ORDER BY t.timestamp) AS LAST_ACTION2,
           LEAD(t.ActionType,1) OVER(PARTITION BY t.userid ORDER BY t.timestamp) AS NEXT_Action,
           LEAD(t.ActionType,2) OVER(PARTITION BY t.userid ORDER BY t.timestamp) AS NEXT_Action2
    FROM YourTable t
    WHERE tmstmp BETWEEN <Start> AND <End>)
WHERE (t.actionType = 'JOIN' AND
      t.NEXT_Action = 'Cancel' AND
      t.NEXT_Action2 = 'JOIN')
  OR (t.LAST_ACTION= 'JOIN' AND
      t.actionType= 'Cancel' AND
      t.NEXT_Action = 'JOIN')
  OR (t.LAST_ACTION2= 'JOIN' AND
      t.LAST_ACTION = 'Cancel' AND
      t.actionType= 'JOIN')