Wsrtjtyk

I have a MySQL DB table that logs user login and logout activities.
I need to select all user records who are not logged back in within 20 minutes after logout.

Ex: Table: Log

ID | User | Event  | Time

-----------------------------

1  | 1    | LOGIN  | 10:00:00

2  | 2    | LOGIN  | 10:05:00

3  | 3    | LOGIN  | 10:15:00

4  | 1    | LOGOUT | 11:00:00

5  | 3    | LOGOUT | 11:01:00

6  | 2    | LOGIN  | 12:20:00

7  | 2    | LOGOUT | 12:30:00

8  | 1    | LOGIN  | 12:31:00

9  | 2    | LOGIN  | 12:55:00

According to this sample table, the users that exceeds the gap by 20 minutes between their logouts and logins must be shown.
User 1 exceeds the 20 minute gap between record 4 and 8
User 2 exceeds the 20 minute gap between record 7 and 9

So this should show

ID | User

----------

4  | 1

8  | 1

7  | 2

9  | 2

How can I write a query to get this done?

This answers the question:

I need to select all user records who are not logged back in within 20 minutes after logout.

select lo.*

from (select l.*,

             (select min(l2.time)

              from logs l2

              where l2.user = l.user and l2.time > l.time and

                    l2.event = 'LOGIN'

             ) as next_login_time

      from logs l

      where l.event  = 'LOGOUT'

     ) lo

where next_login_time > time + interval 20 minute;

Your sample results include login results. It is unclear how those are defined, based on the question that you asked.

You seem to be interested in getting both rows if they are 20 minutes apart. The following tries to emulate LEAD and LAG:

SELECT * FROM (

    SELECT *, (

        SELECT CASE WHEN EVENT = 'LOGOUT' AND main.Event = 'LOGIN' THEN TimeDIFF(main.Time, Time) END

        FROM t AS prev

        WHERE User = main.User AND Time < main.Time

        ORDER BY Time DESC

        LIMIT 1

    ) AS diff_lag, (

        SELECT CASE WHEN EVENT = 'LOGIN' AND main.Event = 'LOGOUT' THEN TimeDIFF(Time, main.Time) END

        FROM t AS next

        WHERE User = main.User AND Time > main.Time

        ORDER BY Time ASC

        LIMIT 1

    ) AS diff_lead

    FROM t AS main

) x

WHERE diff_lag > '00:20:00' OR diff_lead > '00:20:00'

Alternatively, try the following approach which seems to work except that it puts both rows together:

SELECT *

FROM t AS o

INNER JOIN t AS i ON o.User = i.User AND o.Time < i.Time                    -- join logouts with potential logins

LEFT JOIN t AS x ON o.User = x.User AND o.Time < x.Time AND x.Time < i.Time -- any row present between logout and login

WHERE o.Event = 'LOGOUT' AND i.Event = 'LOGIN' AND x.ID IS NULL AND TIMEDIFF(i.Time, o.Time) > '00:20:00'