https://www.youtube.com/watch?v=cdTzo1eZZ50&list=PLavw5C92dz9Hxz0YhttDniNgKejQlPoAn&index=29

drop table if exists login_details;
create table login_details
(
	times	time,
	status	varchar(3)
);
insert into login_details values('10:00:00', 'on');
insert into login_details values('10:01:00', 'on');
insert into login_details values('10:02:00', 'on');
insert into login_details values('10:03:00', 'off');
insert into login_details values('10:04:00', 'on');
insert into login_details values('10:05:00', 'on');
insert into login_details values('10:06:00', 'off');
insert into login_details values('10:07:00', 'off');
insert into login_details values('10:08:00', 'off');
insert into login_details values('10:09:00', 'on');
insert into login_details values('10:10:00', 'on');
insert into login_details values('10:11:00', 'on');
insert into login_details values('10:12:00', 'on');
insert into login_details values('10:13:00', 'off');
insert into login_details values('10:14:00', 'off');
insert into login_details values('10:15:00', 'on');
insert into login_details values('10:16:00', 'off');
insert into login_details values('10:17:00', 'off');

select * from login_details;

WITH
SUBQUERY
TIMESTAMPDIFF()

WITH cte as (
SELECT distinct first_value(times) OVER (partition by grp) AS login,
last_value(times) OVER (partition by grp) AS last_login
FROM (
SELECT * 
,rn - row_number() OVER (order by times) AS grp
FROM
(
SELECT *, 
row_number() OVER (order by times) AS rn
FROM login_details
) x
WHERE status = 'on'
) y
),
cte_final AS (
SELECT login
,LEAD(times) OVER (order by times) AS logoff
FROM login_details ld LEFT JOIN cte ON ld.times = cte.last_login
)
SELECT *,timestampdiff(minute,login,logoff) AS Duration FROM cte_final where login IS NOT NULL;