[250915] 스파르타코딩 본캠프 30일차

💥  CODEKATA                                                                                                           

~ 90문제

 

 

오늘의 코드카타 후기 :

서브테이블사랑단 가입하실 분 (1/N)

 

 

 

89) 570. Managers with at Least 5 Direct Reports

# 내코드
WITH manager AS (
    SELECT a.managerId
    FROM (
        SELECT *, count(1) as cnt
        FROM employee
        GROUP BY managerId
    ) a
    WHERE a.cnt >= 5
)
SELECT e.name
FROM employee e JOIN manager m ON e.id = m.managerid

# 효율 1등코드
SELECT e2.name
FROM Employee e1 JOIN Employee e2 ON e1.managerId = e2.id
GROUP BY e1.managerId
HAVING COUNT(e1.id) >= 5

⇒

아 HAVING으로도 되는구나…. HAVING 맨날 까먹고 안씀;;

이정도면 헤빙혐오단임

 

 

90) 1934. Confirmation Rate

# 내코드
WITH total AS (
    SELECT 
        a.user_id, SUM(a.cnt) AS totalcnt, 
        SUM((a.if_action * a.new_action)) AS calmessage
    FROM (
        SELECT 
            *, COUNT(1) AS cnt, IF(action="timeout", 0, 1) AS if_action, 
            IF(action="timeout", 0, 1)*COUNT(1) as new_action
        FROM confirmations
        GROUP BY user_id, action
    ) a
    GROUP BY a.user_id
),
jointotal AS (
    SELECT 
        s.user_id, ROUND((t.calmessage / t.totalcnt), 2) AS confirmation_rate
    FROM signups s LEFT JOIN total t ON s.user_id = t.user_id
) 
SELECT user_id, IF(confirmation_rate IS NULL, 0.00, confirmation_rate) AS confirmation_rate
FROM jointotal

# 효율 1등코드
SELECT s.user_id, ROUND(AVG(IF(c.action='confirmed',1,0)),2) AS confirmation_rate
FROM signups s LEFT JOIN confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id;

⇒ 아쒸,,.,, 로직은 같은데 저걸 SELECT 문에서 한 줄로 줄일 수가 있네 ㄱ-