[SQL 코드카타] 113 ~ 115번 (LeetCode)

113번 employees-whose-manager-left-the-company

select
    employee_id
from
    employees
where
    salary < 30000 -- 1) less than
    and manager_id not in (select employee_id -- 2) not in employee_id
                        from employees)
order by
    employee_id

> join을 활용한 해답

SELECT 
    e1.employee_id
FROM  -- 1) left 조인
    Employees e1
    LEFT JOIN Employees e2
    ON e1.manager_id = e2.employee_id
WHERE  
    e1.salary < 30000  -- 2) 조건 1
    AND e1.manager_id IS NOT NULL 3) 우선 manager가 아닌 employee_id는 제외
    AND e2.employee_id IS NULL 4) 매니저 id에는 있지만 employee_id는 없는
ORDER BY 
    employee_id;

 

114번 exchange-seats

select
    case when id % 2 = 0 then id - 1 -- 1) 짝수면 id -1
        when id = (select count(*) from seat) then id -- 2) 홀수이면서 마지막 id일 때는 id
        else id + 1 -- 3) 그리고 나머지 홀수는 id + 1
        end as id,
    student
from
    seat
order by
    1;

 

115번 movie-rating

(select
    us.name as results
from
    movierating mr
    join users us
    on mr.user_id = us.user_id
group by
    mr.user_id
order by
    count(mr.rating) desc, us.name asc
limit 1)
union all
(select
    mo.title as results
from
    movierating mr
    join movies mo
    on mr.movie_id = mo.movie_id
where
    date_format(created_at, '%Y-%m') = '2020-02'
group by
    mo.movie_id
order by
    avg(mr.rating) desc, mo.title asc
limit 1)