How to calculate the total length of time between rows of a certain selection in MySQL?

Question

MySQL

Good afternoon, please tell me where to dig, has this kind of

user table; datetime ; event
user1 ; 03/23/2022 00:00:00 ; 1
user1 ; 03/23/2022 00:10:00 ; 2
user1 ; 03/23/2022 00:15:00 ; 1
user2 ; 03/23/2022 00:20:00 ; 1
user1 ; 03/23/2022 00:30:00 ; 2
user2 ; 03/23/2022 00:30:00 ; 1

in this case, event 1 is the user's login, 2 is the output , the
task is such that you need to calculate the total time of the users' work.
Those. if you look at the example above, for the month of March, the first user worked 25 minutes in total, and the second user 10 minutes

Somehow you need to group and aggregate information by duration between paired rows for each user and so that the event goes in a certain sequence, i.e. only between 1 and 2 (not between 2 and 1)

Answer 1

WITH cte AS ( SELECT *, 
                     LAG(`datetime`) OVER (PARTITION BY login ORDER BY `datetime`) lag_datetime, 
                     LAG(event) OVER (PARTITION BY login ORDER BY `datetime`) lag_event 
              FROM history )
SELECT login, SUM(TIMESTAMPDIFF(MINUTE, lag_datetime, `datetime`)) duration
FROM cte
WHERE (event, lag_event) = (2,1)
GROUP BY login;

https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=398... (original data corrected).