Confirmation Rate

Confirmation Rate

문제 내용

Signups Table과 Confirmations Table 두개가 있을 때

모든 user에 대한 login 성공 확률을 나타내라.

Signups Table

Column Name	Type
user_id	int
time_stamp	datetime

Confirmations Table

Column Name	Type
user_id	int
time_stamp	datetime
action	ENUM ('confirmed', 'timeout')

 

접근 방법

union all 을 이용해서 접근 가능하다.

우선 필요한것은 

Confirmations Table을 이용해서 user별 'confirmed'의 횟수와 'timeout'을 포함한 전체 횟수를 구하는 것이다.

궁극적으로 성공확률은

• 성공한 횟수 / (성공한 횟수 + 실패한 횟수)

로 나타낼 수 있기 때문이다.

select A.user_id, round(sum(A.confirmed) / (sum(A.confirmed) + sum(A.timeout)),2) confirmation_rate
from(
select user_id, count(action) confirmed, 0 timeout
from Confirmations
where action = "confirmed"
group by user_id

union all

select user_id, 0 confirmed, count(action) timeout
from Confirmations
where action = "timeout"
group by user_id
) A
group by user_id

union all을 이용해서 2개의 action을 각각 구하고 이를 sum해서 confirmation_rate를 표현 하는 것을 볼 수 있다.

 

user_id signup만하고 로그인 시도 조차 하지 않은 경우가 있기 때문에, Signups Table로 left join이 필요하다.

그리고 IFNULL을 통해서 user_id가 count 되지 않는 컬럼이 있다면 0으로 표현되게 처리해 준다.

select SG.user_id user_id, IFNULL(CO.confirmation_rate,0) confirmation_rate
from Signups SG
        left join 
                    ( select A.user_id, round(sum(A.confirmed) / (sum(A.confirmed) + sum(A.timeout)),2) confirmation_rate
                        from(
                            select user_id, count(action) confirmed, 0 timeout
                            from Confirmations
                            where action = "confirmed"
                            group by user_id

                            union all

                            select user_id, 0 confirmed, count(action) timeout
                            from Confirmations
                            where action = "timeout"
                            group by user_id
                        ) A
                    group by user_id
                    ) CO
on SG.user_id = CO.user_id

이게 복잡하게 풀어본 SQL 문제라고 하면, 아주 간단해게 해당 SQL을 아래와 같이 풀수 있다.

 

left join을 진행한다.

전체 count를 sum하고 confirmed가 된 값만 count해서 나누어 준다.

select A.user_id, round(IFNULL(sum(B.action = 'confirmed') / count(*),0),2) confirmation_rate
from Signups A left join Confirmations B on A.user_id = B.user_id
group by user_id

굉장히 짧은 것을 확인 할 수 있는데, 기본적인 착안은

sum(B.action = 'confirmed') 가 가능하다는 것이다.

inner sql을 통하거나 union all을 통해 별도 view table을 만들어 주지 않아도 부분 sum이 쉽게 가능한 것을 확인 할 수 있다.