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

drop table if exists emp_attendance;
create table emp_attendance
(
	employee 	varchar(10),
	dates 		date,
	status 		varchar(20)
);
insert into emp_attendance values('A1', '2024-01-01', 'PRESENT');
insert into emp_attendance values('A1', '2024-01-02', 'PRESENT');
insert into emp_attendance values('A1', '2024-01-03', 'PRESENT');
insert into emp_attendance values('A1', '2024-01-04', 'ABSENT');
insert into emp_attendance values('A1', '2024-01-05', 'PRESENT');
insert into emp_attendance values('A1', '2024-01-06', 'PRESENT');
insert into emp_attendance values('A1', '2024-01-07', 'ABSENT');
insert into emp_attendance values('A1', '2024-01-08', 'ABSENT');
insert into emp_attendance values('A1', '2024-01-09', 'ABSENT');
insert into emp_attendance values('A1', '2024-01-010', 'PRESENT');
insert into emp_attendance values('A2', '2024-01-06', 'PRESENT');
insert into emp_attendance values('A2', '2024-01-07', 'PRESENT');
insert into emp_attendance values('A2', '2024-01-08', 'ABSENT');
insert into emp_attendance values('A2', '2024-01-09', 'PRESENT');
insert into emp_attendance values('A2', '2024-01-010', 'ABSENT');

SELECT * from emp_attendance;

WITH
FIRST_VALUE()
ROW_NUMBER()

with cte as(
SELECT 
	*,
    row_number() OVER (partition by employee order by dates) as rn
from emp_attendance
)

,cte2 as (
select 
	employee,
    dates,
    status,
    rn - 
	row_number() OVER (partition by employee order by dates) as rn1
from cte 
where status = "Present"
)

,cte3 as (
select 
	employee,
    dates,
    status,
    rn - 
	row_number() OVER (partition by employee order by dates) as rn1
from cte 
where status = "Absent"
)


select 
	employee,
    first_value(dates) OVER (partition by employee,rn1 order by dates) as From_date,
    last_value(dates) OVER (partition by employee,rn1 order by dates range between unbounded preceding and unbounded following) as To_date,
    status
from cte2 

UNION 

select 
	employee,
    first_value(dates) OVER (partition by employee,rn1 order by dates) as From_date,
    last_value(dates) OVER (partition by employee,rn1 order by dates range between unbounded preceding and unbounded following) as To_date,
    status
from cte3
order by employee,from_date