By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `history` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`login` varchar(64) DEFAULT NULL,
`datetime` datetime DEFAULT NULL,
`event` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `history` (`id`, `login`, `datetime`, `event`) VALUES
(1, 'user1', '2022-03-23 00:00:00', 1),
(2, 'user1', '2022-03-23 00:10:00', 2),
(3, 'user1', '2022-03-23 00:15:00', 1),
(4, 'user2', '2022-03-23 00:20:00', 1),
(5, 'user1', '2022-03-23 00:30:00', 2),
(6, 'user2', '2022-03-23 00:30:00', 2);
Records: 6 Duplicates: 0 Warnings: 0
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
| login | duration |
|---|---|
| user1 | 25 |
| user2 | 10 |