MySQL 8.0

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

select version();


version()


8.0.36


create table t
(department_id int,employee_id int);

insert into t values 
(1,1),(1,2),(1,3),
(2,4),(2,5);

Records: 5  Duplicates: 0  Warnings: 0


SELECT department_id, COUNT(employee_id) no_employee, 
    RANK() OVER (ORDER BY COUNT(employee_id) DESC ) Rank_ 
FROM t 
GROUP BY department_id;


department_id
no_employee
Rank_


1
3
1

2
2
2