Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table EMPLOYEE (EMP_ID, EMP_DATE, EMP_TASK) as
select 'A1', date '2024-04-01', 345 from dual union all
select 'A2', date '2024-04-01', 546 from dual union all
select 'A3', date '2024-04-01', 232 from dual union all
select 'A4', date '2024-04-01', 8000 from dual union all
select 'A5', date '2024-04-01', 2344 from dual union all
select 'A1', date '2024-04-02', 456 from dual union all
select 'A2', date '2024-04-02', 9280 from dual union all
select 'A3', date '2024-04-02', 324 from dual union all
select 'A2', date '2024-04-02', 754 from dual union all
select 'A8', date '2024-04-02', 75 from dual union all
select 'A2', date '2024-04-03', 400 from dual union all
select 'A3', date '2024-04-03', 234 from dual union all
select 'A3', date '2024-04-04', 100 from dual
13 rows affected

select emp_id, emp_date,
  emp_date - min(emp_date) over() + 1 as day_num,
  count(*) over (partition by emp_id order by emp_date) emp_day_num
from (
  select distinct emp_id, emp_date
  from employee
)
order by emp_date, emp_id


EMP_ID
EMP_DATE
DAY_NUM
EMP_DAY_NUM


A1
01-APR-24
1
1

A2
01-APR-24
1
1

A3
01-APR-24
1
1

A4
01-APR-24
1
1

A5
01-APR-24
1
1

A1
02-APR-24
2
2

A2
02-APR-24
2
2

A3
02-APR-24
2
2

A8
02-APR-24
2
1

A2
03-APR-24
3
3

A3
03-APR-24
3
3

A3
04-APR-24
4
4


select emp_date, count(emp_id)
from (
  select emp_id, emp_date,
    emp_date - min(emp_date) over() + 1 as day_num,
    count(*) over (partition by emp_id order by emp_date) emp_day_num
  from (
    select distinct emp_id, emp_date
    from employee
  )
)
where emp_day_num = day_num
group by emp_date
order by emp_date


EMP_DATE
COUNT(EMP_ID)


01-APR-24
5

02-APR-24
3

03-APR-24
2

04-APR-24
1

EMP_ID	EMP_DATE	DAY_NUM	EMP_DAY_NUM
A1	01-APR-24	1	1
A2	01-APR-24	1	1
A3	01-APR-24	1	1
A4	01-APR-24	1	1
A5	01-APR-24	1	1
A1	02-APR-24	2	2
A2	02-APR-24	2	2
A3	02-APR-24	2	2
A8	02-APR-24	2	1
A2	03-APR-24	3	3
A3	03-APR-24	3	3
A3	04-APR-24	4	4