请尝试以下查询：
示例数据：
临时表：

create temp table temp_emp 
( 
    employee_id int,
    date date
);

字符串
样本数据：

insert into temp_emp values(1,'2023-08-02');
insert into temp_emp values(2,'2023-08-01');
insert into temp_emp values(3,null);
insert into temp_emp values(4,'2023-07-31');
insert into temp_emp values(5,'2023-07-29');
insert into temp_emp values(6,null);
insert into temp_emp values(7,'2023-08-03');
insert into temp_emp values(8,'2023-07-03');
insert into temp_emp values(9,'2023-01-01');
insert into temp_emp values(10,null);

型
查询：

;with cte as
(
    SELECT *,
    ROW_NUMBER() over (partition by (case when date <=  now() then 1 else null end) order by date desc) rnk
    from temp_emp
)
select date, case when rnk <= 5 and date <=now() then rnk else null end as ranks
from cte;

型
测试结果：

的数据

我认为您还需要列出employee列，例如employee id（您做了select *）。如果是这样，你需要这样的东西：

select main.* from (select distinct emp from test ) t1,
  lateral(select emp, dt, row_number() over (partition by emp order by dt desc) rn
  from test t2 where t1.emp = t2.emp
  and t2.dt <= now()
  group by t2.emp, t2.dt
  order by t2.dt desc
  limit 5) main
  order by 1, 2 desc;

字符串
在横向连接中，您可以获得每个组的最后5个日期，然后对其进行迭代。
Fiddle
此外，还有一个不同的版本：

with main as (select emp, dt from test
group by emp, dt
  having dt <= now()), final as(
  select emp, dt, row_number() over (partition by emp order by dt desc) rn
  from main where dt <= now())
  select * from final where rn <= 5;

型
为了比较它们，我增加了数据量，您可以看到使用fiddle的两个查询的解释输出的比较。
简而言之，第二次查询更快。
注：答案中有错误，已修复。