FAANG SQL Interview Question | SQL Intermediate Question 13

Hey guys, in this video i am solving a FAANG Interview question.
Do try it by yourself and comment bellow your answers.
DDL Commands :-
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');
#faang #facebook #apple #amazon #netflix #google #interviewquestions #dataanalytics #analytics #dataengineering #placement #college #ai

[@Code-Con]

My approach :-
with cte as(
select *, lead(flag,1) over(partition by empd_id order by swipe) as nxt_flag,
lead(swipe,1) over(partition by empd_id order by swipe) as nxt_swipe
from clocked_hours),
cte2 as(
select *,
case
when flag='I' and nxt_flag='O' and nxt_swipe>swipe then datediff(HH,swipe,nxt_swipe)
else 0
end as diff
from cte)
select empd_id, sum(diff) as clocked_hours
from cte2
group by empd_id;
Comment down yours too!

[@Savenature635]

My approach :
with cte as (select *,lead(flag) over(partition by empd_id order by swipe) as next_flag,
lead(swipe) over(partition by empd_id order by swipe) as next_log
from clocked_hours)
select empd_id,cast(sum(case when flag='I' and next_flag='O' then cast(next_log-swipe as time) else 0 end) as time) as
total_clocked_hrs
from cte
group by 1;

[@tamojeetchatterjee9385]

My solution
with cte as (select empd_id , swipe_time , flag as f , lag(swipe_time) over(partition by empd_id) as new_time
from clocked_hours)
select empd_id , extract(hour from sum(swipe_time - new_time)) as clicked_hrs from cte
where f = 'O' group by empd_id

[@gowrikm-n5u]

select empd_id, sum(diff) from
(
select empd_id, swipe,flag,
case when flag = 'I' then datediff(hour,swipe,lead(swipe) over(partition by empd_id order by swipe))
else 0
end as diff
from clocked_hours)m
group by m.empd_id

[@girishtb6984]

WITH CTE AS
(SELECT * ,lead(swipe,1) over(partition by empd_id order by swipe) as nxt_swipe
FROM clocked_hours)
SELECT empd_id,sum(DATEDIFF(hh,swipe,nxt_swipe))
FROM CTE
WHERE FLAG='I'
group by empd_id

[@MubarakAli-qs9qq]

My solution 😢😢😢😢😢😢😢