https://www.youtube.com/watch?v=JfwdgNvETXo

create table clocked_hours(
empd_id int,
swipe time,
flag char
);

insert into clocked_hours values
(11114,'08:30','I'),
(11114,'10:30','O'),
(11114,'11:30','I'),
(11114,'15:30','O'),
(11115,'09:30','I'),
(11115,'17:30','O');

WITH
ROW_NUMBER()

-- Approach 1
with cte as (
select * 
	,row_number() OVER (partition by empd_id,flag order by swipe) as rn
from ch
),cte2 as (
select 
	empd_id,min(swipe) as swipe_in,max(swipe) as swipe_out,
    timestampdiff(hour,min(swipe),max(swipe)) AS clocked_hours
FROM cte
GROUP BY
empd_id,rn
)
select empd_id,SUM(clocked_hours) as clocked_hours
from cte2
group by empd_id;

LEAD()

-- Approach 2
with cte as (
select * 
	,lead(swipe,1) OVER (partition by empd_id order by swipe) AS swipe_out
from ch
)
select 
	empd_id,
    SUM(timestampdiff(hour,swipe,swipe_out)) as time_in
from cte
where flag = 'I'
GROUP BY empd_id