drop table if EXISTS Project;
create table Project(
project_id int,
employee_id int
);
drop table if EXISTS Employee;
create table Employee(
employee_id int,
name varchar(20),
experience_years int
);
insert into Project values(1, 1);
insert into Project values(1, 2);
insert into Project values(1, 3);
insert into Project values(2, 1);
insert into Project values(2, 4);
insert into Employee values(1, 'Khaled', 3);
insert into Employee values(2, 'Ali', 2);
insert into Employee values(3, 'John', 1);
insert into Employee values(4, 'Doe', 2);
select project_id
from Project
group by project_id
having count(employee_id
)>=all(select count(employee_id
) from Project
group by project_id
);
select project_id
from (
select project_id
, count(employee_id
), dense_rank
() over(order by count(employee_id
) desc) rk
from Project
GROUP BY project_id
) t
where t
.rk
= 1
