sql_코드카타(2024.01.25)

김수경·2024년 1월 25일

코드카타

목록 보기

19/29

89. Managers with at Least 5 Direct Reports

테이블 : Employee

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
| department | varchar |
| managerId | int |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table indicates the name of an employee, their department, and the id of their manager.
If managerId is null, then the employee does not have a manager.
No employee will be the manager of themself.

문제

5명 이상의 직속 부하 직원이 있는 관리자를 찾는 솔루션을 작성하세요 .
어떤 순서로든 결과 테이블을 반환합니다 .

Output

+------+
| name |
+------+
| John |
+------+

👩🏻‍💻 My Coding

select 
    case when managerID is null then name
    end as name
from employee 
having count(department) >= 5

managerID가 없는 사람이 관리자라 판단하고 having과 조건문으로 쿼리를 짰지만 오답이다.
문제의 해석이 잘못됐다.
id와 managerID를 조인을 통해 활용하는 것이 포인트!
그럼 id와 managerID가 동일하면서 managerID가 5개 이상인 것을 뽑아내는 것

select a.name
from employee a inner join employee b on a.id=b.managerid
group by b.managerid
having count(b.managerid) >= 5

뭔가 조인은 직관적이지가 않다.
서브쿼리를 활용한 답을 참고해보면
서브쿼리에서 managerid가 5개 이상인 것을 골라내고
메인쿼리에서 골라낸 managerid와 같은 id가 있는 것을 찾아내는 방법이 있다.

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

복잡하지 않은 쿼리문인데 어떤식으로 구조를 짜야 할지를 잘 고민하고 문제를 풀어야겠다.

90. Confirmation Rate

테이블1 : Signups

+----------------+----------+
| Column Name | Type |
+----------------+----------+
| user_id | int |
| time_stamp | datetime |
+----------------+----------+
user_id is the column of unique values for this table.
Each row contains information about the signup time for the user with ID user_id.

테이블2 : Confirmations

+----------------+----------+
| Column Name | Type |
+----------------+----------+
| user_id | int |
| time_stamp | datetime |
| action | ENUM |
+----------------+----------+
(user_id, time_stamp) is the primary key (combination of columns with unique values) for this table.
user_id is a foreign key (reference column) to the Signups table.
action is an ENUM (category) of the type ('confirmed', 'timeout')
Each row of this table indicates that the user with ID user_id requested a confirmation message at time_stamp and that confirmation message was either confirmed ('confirmed') or expired without confirming ('timeout').

문제

사용자의 확인 비율은'confirmed' 메시지 수를 요청한 전체 확인 메시지 수로 나눈 값입니다. 확인 메시지를 요청하지 않은 사용자의 확인 비율은 입니다 0. 확인률을 소수점 이하 두 자리로 반올림합니다.
각 사용자의 확인률을 구하는 솔루션을 작성하세요.
어떤 순서로든 결과 테이블을 반환합니다.

output

+---------+-------------------+
| user_id | confirmation_rate |
+---------+-------------------+
| 6 | 0.00 |
| 3 | 0.00 |
| 7 | 1.00 |
| 2 | 0.50 |
+---------+-------------------+

👩🏻‍💻My Coding

select s.user_id,
    round(count(case when c.action = 'confirmed' then 1 end)/count(s.user_id),2) as confirmation_rate 
from signups s left join confirmations c on s.user_id = c.user_id 
group by s.user_id