Untitled

Matching user actions from a MySQL table: each CLOSE should match the previous OPEN for a given user
TABLE USER_ACTIONS
------------------------
USER | TYPE  | TIMESTAMP
------------------------
a    | OPEN  | 0
b    | OPEN  | 1
a    | CLOSE | 2
a    | OPEN  | 3
b    | CLOSE | 4
a    | CLOSE | 4
a    | OPEN  | 5  <-- "orphaned" OPEN, with no corresponding CLOSE. Should be ignored.
c    | OPEN  | 3
c    | CLOSE | 5
a    | OPEN  | 6
a    | CLOSE | 8

USER | TRANSACTION_TIME
-----------------------
a    | 2
b    | 3
a    | 1
c    | 2
a    | 2

for each CLOSE_ACTION IN ("SELECT USER, TYPE, TIMESTAMP FROM USER_ACTIONS WHERE TYPE='CLOSE' ORDER BY TIMESTAMP DESC;") {
    OPEN_ACTION = "SELECT USER, TYPE, TIMESTAMP FROM USER_ACTIONS
                   WHERE TYPE='OPEN'
                   AND USER='<CLOSE_ACTION.USER>'
                   AND TIMESTAMP='<CLOSE_ACTION.TIMESTAMP>'
                   ORDER BY TIMESTAMP DESC
                   LIMIT 1";
    if OPEN_ACTION != empty/null then {
        print CLOSE_ACTION.USER, CLOSE_ACTION.TIMESTAMP - OPEN_ACTION.TIMESTAMP;
    }
}

SELECT
  OPEN.user,
  OPEN.transaction_time
  CLOSE.transaction_time
FROM
  user_actions as CLOSE
INNER JOIN
  user_actions as OPEN
    ON  OPEN.user = CLOSE.user
    AND OPEN.transaction_time = (SELECT MAX(transaction_time) FROM user_action
                                 WHERE user = CLOSE.user
                                 AND transaction_time < CLOSE.transaction_time
                                 AND type='OPEN')
WHERE
    CLOSE.type = 'CLOSE'

select user,
       timestamp,
       (select min(timestamp)
        from user_actions u
        where a.user = u.user and
              u.type = 'CLOSE' and
              u.timestamp > a.timestamp) - timestamp
from user_actions a
where type = 'OPEN'

select u1.user_name ,u2.timestamp- max(u1.timestamp) difference
from user_actions u1,user_actions u2
where u1.type = 'OPEN' and
u2.type = 'CLOSE' and
u1.timestamp <u2.timestamp and u1.user_name = u2.user_name
group by (u1.user_name , u2.timestamp);