1934. Confirmation Rate / LeetCode, SQL, MS SQL Server

문제

source: LeetCode

The confirmation rate of a user is the number of 'confirmed' messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is 0. Round the confirmation rate to two decimal places.

Write an SQL query to find the confirmation rate of each user.

Return the result table in any order.

 

 

 

 

제출답안(MS SQL Server)

WITH
    ls_signups
        AS (SELECT 
                DISTINCT(user_id)
            FROM 
                signups),

    log_cfrm
        AS (SELECT 
                user_id,
                CASE WHEN action = 'confirmed' THEN 1 ELSE 0 END AS is_confirmed
            FROM 
                confirmations),
    cnt_cfrm
        AS (SELECT
                user_id, 
                SUM(is_confirmed) AS cnt_cfrm,
                COUNT(user_id) AS cnt_rqst
            FROM 
                log_cfrm
            GROUP BY
                user_id)

SELECT 
    rqst.user_id, 
    ISNULL(ROUND(cnt_cfrm*1.0/cnt_rqst , 2), 0.00) AS confirmation_rate
FROM 
    ls_signups AS rqst
    LEFT JOIN cnt_cfrm AS cfrm ON rqst.user_id = cfrm.user_id

 

 

 

풀이(MS SQL Server)

CTE1: Sign_up을 시도한 user_id의 목록

WITH
    ls_signups
        AS (SELECT 
                DISTINCT(user_id)
            FROM 
                signups),

>> 예시에 user_id 6을  보면 signup 테이블에 id가 있어도 confirmation에 없는 경우가 있다.

>> 따라서, 리스트를 별도로 만들고 confirmation table 값을 LEFT JOIN한다

 

CTE2: confirmation 테이블의 action column을 원핫 인코딩

    log_cfrm
        AS (SELECT 
                user_id,
                CASE WHEN action = 'confirmed' THEN 1 ELSE 0 END AS is_confirmed
            FROM 
                confirmations),

>> CASE WHEN 구절을 사용하여 confirmed일 경우 1, 아닐경우 0을 부여

 

CTE3: ID별 confirmation 수와 요청 횟수

    cnt_cfrm
        AS (SELECT
                user_id, 
                SUM(is_confirmed) AS cnt_cfrm,
                COUNT(user_id) AS cnt_rqst
            FROM 
                log_cfrm
            GROUP BY
                user_id)

 

 

Signup 테이블의 id (CTE1)에 CTE3 LEFT JOIN

SELECT 
    rqst.user_id, 
    ISNULL(ROUND(cnt_cfrm*1.0/cnt_rqst , 2), 0.00) AS confirmation_rate
FROM 
    ls_signups AS rqst
    LEFT JOIN cnt_cfrm AS cfrm ON rqst.user_id = cfrm.user_id

>> ISNULL을 이용하여 JOIN 결과가 null인 경우 0.0을 자동 입력

>> cnt_cfrm과 cnt_rqst의 데이터 타입은 INT이므로 나눈 결과는 몫만 나옴

>> 따라서 1.0을 곱해줌으로써 FLOAT로 캐스팅 후 나누기 연산을 수행

 

 

문제 바로가기(MS SQL Server)