MySQL 리트코드 | Confirmation Rate (AVG 함수 내 조건문 사용법)

 

문제

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

• 유저 별 확정 비율(Confirmation Rate) 구하기
  • Confirmation Rate = action이 'confirmed' 된 개수 / 전체 request 개수

 

테이블

Signups	user_id	time_stamp
	3	2025-03-12 10:16:13
	7	2025-01-02 13:57:59
	2	2025-07-24 23:09:44
	6	2025-12-04 10:39:37

Confirmations	user_id	time_stamp	action
	3	2026-02-20 03:30:46	timeout
	3	2026-07-29 14:00:00	timeout
	7	2026-06-12 11:57:29	confirmed
	7	2026-06-13 12:58:28	confirmed
	7	2026-06-14 13:59:27	confirmed
	2	2026-01-06 00:00:00	confirmed
	2	2026-02-22 23:59:59	timeout

• Signups 테이블에 존재하는 유저들의 Confirmation Rate를 구해야 함.
  • (user_id = 3) 두 번의 request 중 둘 다 timeout 됨 → 0 / 2 = 0
  • (user_id = 7) 세 번의 request 중 셋 다 confirmed 됨 → 2 / 2 = 1
  • (user_id = 2) 두 번의 request 중 한 번 confirmed되고, 한 번 timeout 됨 → 1 / 2 = 0.5
  • (user_id = 6) 한 번도 request 하지 않았음. → 0 = 0

 

풀이과정

▶ SUM( ) 사용 정답

# 조인과 유저 별 'confirmed' 비율 계산

• 유저 정보와 request 정보를 LEFT 조인해줌.
  • 결과 테이블에는 request가 없어도 전체 유저 정보도 조회해야 하기 때문.
    ex) 상단 테이블 예시에서는 reqeust가 전혀 없었던 user_id = 6인 유저도 조회되어야 함.
• 유저 별 'confirmed' 비율 = 유저 별 'confirmed' 개수 / 유저 별 전체 request 개수
  • user_id로 GROUP BY 해줌. 
  • 유저 별 'confirmed' 개수 = 유저 별로 action 컬럼값이  'comfirmed'인 경우, 1로 아니면 0으로 두고 SUM 해줌.
  • 유저 별 전체 request 개수 =  user_id 카운트

 

# 정답

• SELECT 문으로 user_id 별로 Confirmation Rate 조회해주기
• NULL인 경우, 0으로 처리
• ROUND 사용해서 소수점 두 자릿수 조건 맞추기

SELECT s.user_id,
       ROUND(IFNULL(SUM(IF(c.action='confirmed', 1, 0)) / COUNT(c.user_id), 0),2) confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id

 

다른 사람 풀이 참고

▶ AVG( ) 사용 정답

# 조인과 유저 별 'confirmed' 비율 계산

• 유저 정보와 request 정보를 LEFT 조인해줌.
• 유저 별 'confirmed' 비율을 AVG(action = 'confirmed')를 활용해 구함
  • user_id로 GROUP BY 해줌. 
  • 유저 별 'confirmed' 비율은 간단하게 AVG() 함수로 평균을 구해주면 되는 문제였음.
  • 여기에서 처음 알게된 것은 AVG() 함수 내부에 조건을 적을 수 있다는 것!
    전체 중 조건에 해당하는 비율을 보여줌.

 

# 정답

• SELECT 문으로 user_id 별로 Confirmation Rate 조회해주기
• NULL인 경우, 0으로 처리
• ROUND 사용해서 소수점 두 자릿수 조건 맞추기

SELECT A.user_id, 
       ROUND(IFNULL(AVG(action = 'confirmed'), 0), 2) AS confirmation_rate
FROM Signups AS A
LEFT JOIN Confirmations AS B ON A.user_id = B.user_id
GROUP BY A.user_id;