#88. [LeetCode / MySQL] 1934. Confirmation Rate

문제

 

입력 코드

1.

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
USING(user_id)
GROUP BY user_id

 

2. WINDOW - PARTITION BY

SELECT 
    DISTINCT s.user_id,
    ROUND(SUM(
        CASE WHEN c.action = 'confirmed' THEN 1 
        ELSE 0 
        END
        ) OVER (PARTITION BY user_id) / COUNT(*) OVER (PARTITION BY user_id), 2) AS confirmation_rate
FROM Signups AS s
LEFT JOIN Confirmations AS c 
ON s.user_id = c.user_id

 

3. 

SELECT 
    s.user_id,
       IFNULL(ROUND(SUM(
           CASE WHEN c.action = 'confirmed' THEN 1 
                ELSE 0 
                END) / COUNT(c.user_id), 2), 0.00) AS confirmation_rate
FROM Signups AS s
LEFT JOIN Confirmations AS c 
    ON s.user_id = c.user_id
GROUP BY s.user_id;

 

 

코드 설명

#SELECT #ROUND #AVG #IF #JOIN #LEFT JOIN #USING #GROUP BY #DISTINCT #ROUND #SUM #CASE #PARTITION BY #WINDOW #COUNT #OVER #IFNULL

 

 

문제 출처

Confirmation Rate - LeetCode