[확인 비율 구하기] Confirmation Rate

 

링크

https://leetcode.com/problems/confirmation-rate/description/

 

문제

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 a solution to find the confirmation rate of each user.

Return the result table in any order.

The result format is in the following example.

정답

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 
on s.user_id = c.user_id
group by user_id;

해설

• '확인' '비확인' 같이 두 가지 경우로 나뉠 경우 문자열을 숫자 1과 0으로 구분
• 1이 나온 평균을 구하여 비율을 구한다

새롭게 이해한 내용

• 상황에 따라 항목을 변경할 수 있다