[문제풀이] Leet code - Confirmation Rate

출처

 

문제

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.

문제에 대한 해석

조회 : 유저아이디, 메시지 확인률(유저당 확인횟수 / 유저당 발송횟수)

조건:  메시지를 보내지 않은 유조의 경우 확인률을 0

정렬 : X

6번의 아이디 처럼 메시지를 발송하지 않는 유저의 확인률은 0이다

풀이(MYSQL)

with base as (
    select s.user_id, ifnull(c.time_stamp,0), ifnull(c.action, 'timeout') as action
    from Signups s left join Confirmations c
    on s.user_id = c.user_id
    union
    select s.user_id, c.time_stamp, c.action
    from Signups s right join Confirmations c
    on s.user_id = c.user_id
), -- 모든 user_id에 대한 액션 : 발송을 하지 않은 유저의 확인수를 0으로 하여 추가하기 위해 full outer join
-- 대체 : left join union right join
confirm_cnt as(
select user_id, action, count(1) as act_cnt
from base
where action  = 'confirmed'
group by user_id, action
), -- 메시지를 확인한 user_id의 수
total_cnt as( 
select user_id, count(1) as total_cnt
from base
group by user_id
) -- 전체 발송한 메시지의 수
select t.user_id , round(ifnull(act_cnt/total_cnt,0)) as confirmation_rate
from total_cnt t left join confirm_cnt c
on t.user_id = c.user_id

 

base	confirm_cnt	total_cnt	answer
모든 유저에 대한 매시지 확인 상태	메시지를 확인한 유저와 확인 횟수	모든 유저에 대한 메시지 발송횟수	모든 유저의 메시지 확인 횟수 / 발송횟수 *단, 메시지를 받지 못한 유저의 확인률은 0

 

다른사람 풀이

SELECT a.user_id, round(ifnull(avg(action = 'confirmed'), 0),2) as confirmation_rate
FROM Signups a
LEFT JOIN Confirmations b
ON a.user_id = b.user_id
GROUP BY a.user_id

 

작동순서

💡SQL 실행 순서는 From -> Where -> Group by -> Having -> Select -> Order by ->결과반환

 

1. FROM 절: Signups 과  Confirmations를 user_id 를 기준으로 left조인하여 ( 모든 Signups의 id출력)
2. GROUP BY 절: Signups 의  user_id로 그룹화 하여
3. SELECT 절: Signups 의  user_id, action이 'confirmed'인 경우의 평균값(2째 자리까지, null값의 경우 0