[MY SQL] 사용자에게 수신된 메세지 중 'confirmed' 한 메세지 비율 구하기

출처: [Leetcode - SQL50] : 1934 Confirmation Rate

링크: https://leetcode.com/problems/confirmation-rate/description/?envType=study-plan-v2&envId=top-sql-50

문제(일부):  사용자가 받은 메세지 중 'confirmed'라고 응답한 비율 구하기

---

1. WITH절, 서브쿼리 활용

쿼리는 길어지지만 가장 먼저 생각난 단순한 방법

1) user_id별로 confirm 메세지의 수 => 서브쿼리

2) user_id별로 받은 메세지의 수 => 서브쿼리

3) 메인 쿼리에서 user_id별로 confirm메세지의 비율 구하기(confrim / all)

4) 위 내용을 WITH절로 임시 테이블 생성, signips테이블과 RIGHT JOIN(메세지를 받지 않은 유저도 포함)

WITH comfirmation_rate AS (
    SELECT c.user_id
            ,ROUND(cc.confirm_cnt / c.all_cnt,2) AS confirmation_rate 
    FROM (SELECT user_id
                ,COUNT(*) AS confirm_cnt
            FROM confirmations 
            WHERE action = "confirmed"
            GROUP BY user_id) AS cc
    INNER JOIN (SELECT user_id
                        ,COUNT(*) AS all_cnt
                FROM confirmations
                GROUP BY user_id) AS c
        ON cc.user_id = c.user_id
)
SELECT s.user_id
        , COALESCE(cr.confirmation_rate,0) AS confirmation_rate 
FROM comfirmation_rate AS cr
    RIGHT JOIN Signups AS s
        ON cr.user_id = s.user_id

2. AVG, IF 활용

runtime이 가장 짧은 풀이법1) signup과 confirmation테이블을 left join(동일하게 메세지를 받지 않는 유지도 포함하기 위해)
2) if문으로 action = 'confirmed'인 경우 1을 아닌 경우를 0으로 두고, avg구함(분자= 1의합, 전체 메세지 수)
    ex) confirmed, time_out, confirmed, time_out => 1,0,1,0 => 2/ 4 => 0.5

select signups.user_id, round(avg(if(Confirmations.action="confirmed",1,0)),2) as confirmation_rate
from signups 
left join confirmations on signups.user_id=confirmations.user_id
group by user_id

3. CASE WHEN 활용

1) 2번과 동일하게 2개 테이블을 join해서 사용
2) case when 구문으로 confirmed인 경우 1을 주고 user_id별로 합 / user_id별 id수를 count

select s.user_id, round(COALESCE(sum(case when c.action = 'confirmed' then 1 else 0 end)/count(c.user_id),0),2) 
as confirmation_rate 
from Signups s 
left join Confirmations c on s.user_id = c.user_id 
group by s.user_id
order by s.user_id;