Leetcode SQL (#1075 – #1077)

select 
p.project_id, 
round(avg(e.experience_years),2) as average_years
from Project p
    left join Employee e 
        on p.employee_id = e.employee_id
group by 1
order by 1

select distinct 
project_id
from(
    select 
    project_id, 
    rank() over(order by employee_count desc) as r
    from(
        select 
        project_id, 
        count(distinct employee_id) as employee_count
        from Project
        group by 1
    ) a
) b
where r = 1

select 
b.project_id, 
b.employee_id
from(
    select 
    a.project_id,
    a.employee_id, 
    a.experience_years, 
    rank() over(partition by project_id order by experience_years desc) as r
    from(
    select 
        e.employee_id, 
        p.project_id, 
        e.experience_years
    from Project p
        right join Employee e 
            on p.employee_id = e.employee_id
    ) a
) b
where b.r = 1 
and b.project_id is not null
order by 1,2