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-10', '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-10', 'ABSENT');

-- with ct as (
-- SELECT *, JULIANDAY(dates)-JULIANDAY(lag(dates,1) over(partition by employee,status order by dates asc)) as flag
--     from emp_attendance
-- )
--     ,ct2 as (
-- select * , sum(case when flag>1 then 1 else 0 end) over(partition by employee, status order by dates) as flg_2
--     from ct
-- )
--     select distinct employee, min(dates) over(partition by employee, status, flg_2) as from_date
--     ,max(dates) over(partition by employee, status, flg_2) as to_date
--     , status
--     from ct2
--     order by 1,2 asc;
with ct as (
select *, row_number() over(partition by employee order by dates asc) as rn
from emp_attendance
)
    ,ct2 as (
select *, row_number() over(partition by employee, status order by dates) as rn_1
    ,rn-row_number() over(partition by employee, status order by dates) as window
    from ct
    )
    select distinct employee, min(dates) over(partition by employee, window) as from_date
    , max(dates) over(partition by employee, window) as to_date
    ,status
    from ct2
    order by 1,2;