LeetCode 1075, 1076,1077 Project Employees

drop table Project
drop table Employee
Create table Project (project_id int, employee_id int)
Create table Employee (employee_id int, name varchar(20), experience_years int)

insert into Project (project_id, employee_id) values ('1', '1')
insert into Project (project_id, employee_id) values ('1', '2')
insert into Project (project_id, employee_id) values ('1', '3')
insert into Project (project_id, employee_id) values ('2', '1')
insert into Project (project_id, employee_id) values ('2', '4')

insert into Employee (employee_id, name, experience_years) values ('1', 'Khaled', '3')
insert into Employee (employee_id, name, experience_years) values ('2', 'Ali', '2')
insert into Employee (employee_id, name, experience_years) values ('3', 'John', '1')
insert into Employee (employee_id, name, experience_years) values ('4', 'Doe', '2')

select * from Project
select * from Employee

 

1075. Project Employees I

Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.

 

select project_id,round(cast(AVG(e.experience_years) as float),2) as average_years
from Project p join Employee e 
on p.employee_id = e.employee_id
group by p.project_id

 

1076. Project Employees II

Write an SQL query that reports all the projects that have the most employees.

 

CTE

with cte as(
select project_id,count(employee_id) as people
from Project
group by project_id
),cte2 as
(select project_id,RANK()over(order by people desc) as rnk from cte
)select project_id from cte2 where rnk=1

 

Regular:

select project_id
from Project
group by project_id
having count(employee_id)=
(
select top 1 count(distinct employee_id) 
from Project 
group by project_id
)

 

1077. Project Employees III

Write an SQL query that reports the most experienced employees in each project. In case of a tie, report all employees with the maximum number of experience years.

 

CTE

with cte as(
select p.project_id,e.employee_id,RANK() over(partition by p.project_id order by e.experience_years desc) as rnk
from Project p join Employee e
on p.employee_id=e.employee_id
)select project_id,employee_id from cte where rnk=1