SQL: Leet code 문제풀이(570, 1934, 620)

570. Managers with at least 5 direct reports 

https://leetcode.com/problems/managers-with-at-least-5-direct-reports/description/

 

#구할 값: name 

# 조건절에서 managerId가 5번 이상 언급되는 값을 찾아서 집계해준다.

select name
from Employee
where id in 
    (select managerId
    from Employee
    group by managerId
    having count(*) >= 5)

 

 

1934. Confirmation Rate

https://leetcode.com/problems/confirmation-rate/description/

 

각 사용자의 확인율을 구하고, 소수 2째자리에서 반올림한다. 

확인율은 'confirmed'된 확인 메시지의 평균

select s.user_id, 
       round(avg(if(c.action="confirmed",1,0)),2) as confirmation_rate
from Signups as s left join Confirmations as c on s.user_id= c.user_id
group by user_id;

 

 

620. Not Boring Movies 

https://leetcode.com/problems/not-boring-movies/description/

 

Cinema 테이블에서 홀수로 된 id 값, description이 boring으로 응답한 관객을 찾으면 되는 문제

select *
from Cinema 
where mod(id,2) = 1 and description <> 'boring'
order by rating desc

 

✅MOD함수

홀수 행 검색

select *
from table
where mod(number,2) = 1

 

짝수 행 검색

select * 
from table
where mod(number, 2) = 0