[코드카타] SQL 90번, 91번

90번. Confirmation Rate

Write a solution to find the confirmation rate of each user.

(링크)

문제 분석

signups 테이블 : USER_ID와 사용자들이 sign up한 시간이 입력되있음
confirmations 테이블 : (user_id, time_stamp) 형태의 PK. action 컬럼에 confirmed, timeout 값이 입력되어 있음
confirmation rate = confirmed인 숫자 / 메시지를 받은 사람 수
소수점 둘째자리까지 표시

각 사용자별로 confirmation rate를 구하여라
(confirmed가 0회라면 0.00으로 출력되어야 함. confirmations 테이블에 없어도 0.00)

풀이 구상

일단 signups 테이블로 LEFT JOIN을 해서 ID는 다 받아야 할 것이고,
1. confirmations 테이블에 존재는 하는 경우
count(action)에 where action=confirmed을 걸면 confirmed만 나오겠지? 이게 null이면 0으로 출력하면 될 것이고
2. confirmations 테이블에 존재하지 않는 경우
바로 0으로 진행

코드 작성

아래 코드는 id별로 tot_cnt가 나오지 않아 confirmation_rate를 뽑을 수 없다

SELECT
    *
    , count(c.action) as 'cnt'
    , count(*) as 'tot_cnt'
FROM
    Signups s
    LEFT JOIN (
        SELECT
            *
        FROM 
            confirmations
        WHERE
            action = 'confirmed'
    ) c
        ON s.user_id = c.user_id
GROUP BY
    s.user_id
;

그렇기에 아래 코드를 새로 만들어서 위 코드와 JOIN을 해주기로 했다

SELECT
    *
    , count(1)
FROM 
    signups s
    LEFT JOIN confirmations c
        ON s.user_id = c.user_id
GROUP BY
    s.user_id

다만 이렇게 하면 코드가 비효율적으로 작성되었기에 runtime이 상당히 낮게 나온다.
추후 개선이 필요할 듯.

SELECT
    aa.user_id
#    , aa.cnt
#    , bb.tot_cnt
    , round(aa.cnt / bb.tot_cnt, 2) as 'confirmation_rate'
FROM (
        SELECT
            s.user_id
            , count(c.action) as 'cnt'
        FROM
            Signups s
            LEFT JOIN (
                SELECT
                    *
                FROM 
                    confirmations
                WHERE
                    action = 'confirmed'
            ) c
                ON s.user_id = c.user_id
        GROUP BY
            s.user_id
        ) aa
    INNER JOIN (
        SELECT
            s.user_id
            , count(1) as 'tot_cnt'
        FROM 
            signups s
            LEFT JOIN confirmations c
                ON s.user_id = c.user_id
        GROUP BY
            s.user_id
        ) bb
        ON aa.user_id = bb.user_id
;

91번. Not Boring Movies

Write a solution to report the movies with an odd-numbered ID and a description that is not "boring".
Return the result table ordered by rating in descending order.

(링크)

풀이 구상

number_id가 홀수이면서, description이 boring이 아닌 영화 찾기

rating으로 내림차순
그냥 where절이면 되지 않을까?
ID에 % 연산자로 나머지 구해서 =1이면 될 것이고
description은 !=boring하면 될 것 같은데

코드 작성

SELECT
    *
FROM
    cinema
WHERE
    (id % 2 = 1)
    AND (description != 'boring')
ORDER BY
    rating desc
;