sql - Is it possible in MySQL to use a query that bypass a row if is duplicate and get the very last one?

Question

Is it possible to use a condition in MySQL query that bypass 1 row if the clause is not accomplished. Below is a possible scenario on Vicidial system:

+--------------+---------------------+-----------------+----------------+
| username     | event_date          | event           |event_epoch_time|
+--------------+---------------------+-----------------+----------------+
|         admin| 2020-12-29 14:59:08 | login           |1609253948      |
|         admin| 2020-12-29 15:00:06 | logout          |1609254006      |
|         admin| 2020-12-29 15:14:51 | login           |1609254891      |
|         admin| 2020-12-29 15:15:00 | login           |1609254900      |
|         admin| 2020-12-29 15:59:01 | logout          |1609257541      |
+--------------+---------------------+-----------------+----------------+

Here in the table I have 2 logins for the same user, the issue could be happening as the user closed the browser without logout. Is it possible to make a calculation of the SUM (event_epoch_time) where this is the sum of logout-login of each 2 events?

So the total time will be the first 2 rows and the last 2 rows, ignoring the following row

|         admin| 2020-12-29 15:14:51 | login           |1609254891      | 

Answer 1

I understand that you want to ignore consecutive logins, and only consider the last one.

You can use window functions. Here are two approaches using lag(), available in MySQL 8.0.

If a logout is always preceded by a login, you can do:

select username, 
    count(*) as cnt_sessions,
    sum(event_epoch_time - lag_event_epoch_time) as sum_session_duration
from (
    select t.*,
        lag(event_epoch_time) over(partition by username order by event_epoch_time) as lag_event_epoch_time
    from mytable t
) t
where event = 'logout'
group by username

We can also tweak the query so it ignores consecutive logouts:

select username, sum(event_epoch_time - lag_event_epoch_time) as diff
from (
    select t.*,
        lag(event_epoch_time) over(partition by username order by event_epoch_time) as lag_event_epoch_time,
        lag(event) over(partition by username order by event_epoch_time) as lag_event
    from mytable t
) t
where event = 'logout' and lag_event = 'login'
group by username