[LeetCode] 1934. Confirmation Rate (JOIN)

< 문제 >

[SQL50] 1934. Confirmation Rate (JOIN)

---

---

< 풀이 >

WITH all_action AS(
    SELECT user_id, SUM(1) AS action_cnt
    FROM Confirmations
    GROUP BY user_id
),
confirmed AS(
    SELECT user_id, SUM(1) AS confirmed_cnt
    FROM Confirmations
    WHERE action = 'confirmed'
    GROUP BY user_id
),
divide AS(
    SELECT A.user_id, ROUND(B.confirmed_cnt / A.action_cnt ::decimal, 2) AS confirmation_rate
    FROM all_action A LEFT JOIN confirmed B ON A.user_id = B.user_id
    )

SELECT A.user_id, COALESCE(B.confirmation_rate, 0,00) AS confirmation_rate
FROM Signups A LEFT JOIN divide B ON A.user_id = B.user_id;

---

 

WITH AS

임시 테이블 여러개 생성해서 활용

https://youngone-kang.tistory.com/45

ROUND

소수점 둘째 자리까지 반올림하는 연산 활용

https://youngone-kang.tistory.com/51

COALESCE

confirmation_rate가 NULL인 경우 0.00으로 반환

https://youngone-kang.tistory.com/52