[SQL/오답] AVG(조건식) 승인율 계산과 NULL 처리 (LeetCode1934 Medium)

1. Problem

각 유저별로 전체 확인 요청 대비 '확인됨(confirmed)' 상태의 비율을 계산해야 한다. 요청 기록이 전혀 없는 유저의 승인율은 0으로 처리해야 하며, 소수점 둘째 자리까지 반올림하는 정밀도가 요구된다. 가입만 하고 요청은 하지 않은 유저를 결과에서 누락시키지 않는 것이 기술적 관건이다.

2. Solution

LEFT JOIN을 통해 모든 유저를 확보한 후, AVG 함수 내부에 직접 조건을 삽입하여 연산 효율을 극대화한다.

• AVG(c.action = 'confirmed'): MySQL에서 조건식은 참일 때 1, 거짓일 때 0을 반환한다. 이를 평균내면 별도의 분모 설정 없이도 바로 비율(Rate)이 계산된다.
• IFNULL(..., 0): LEFT JOIN으로 인해 요청 기록이 없는 유저(ID 6)는 AVG 값이 NULL이 되는데, 이를 비즈니스 요구사항에 맞춰 0으로 치환한다.

3. Takeaway (데이터 분석적 최적화 인사이트)

비율 데이터를 다룰 때 분석가가 범하기 쉬운 실수와 이를 방지하는 효율적인 접근법을 정립한다.

• 서브쿼리 남용의 위험: SELECT 절에 그룹화된 서브쿼리를 넣으면 "Subquery returns more than 1 row" 에러가 발생하기 쉽다. 집계 함수 내부에서 논리 연산을 처리하는 '조건부 집계(Conditional Aggregation)'가 훨씬 안전하고 빠르다.
• AVG(조건식)의 마법: 실무에서 클릭률(CTR), 구매 전환율(CVR) 등을 구할 때 SUM(CASE WHEN...) / COUNT(*) 대신 AVG(조건식)을 사용하면 쿼리의 가독성이 비약적으로 상승한다.
• 데이터 정합성 유지: Signups 테이블을 기준으로 LEFT JOIN 하는 이유는 요청을 하지 않은 '잠재적 활동 유저'까지 지표에 포함하기 위함이다. 이는 지표의 왜곡을 방지하는 분석가의 기본 소양이다.

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

 

 

Table: Signups

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
+----------------+----------+
user_id is the column of unique values for this table.
Each row contains information about the signup time for the user with ID user_id.
 

Table: Confirmations

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
| action         | ENUM     |
+----------------+----------+
(user_id, time_stamp) is the primary key (combination of columns with unique values) for this table.
user_id is a foreign key (reference column) to the Signups table.
action is an ENUM (category) of the type ('confirmed', 'timeout')
Each row of this table indicates that the user with ID user_id requested a confirmation message at time_stamp and that confirmation message was either confirmed ('confirmed') or expired without confirming ('timeout').
 

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.

 

Example 1:

Input: 
Signups table:
+---------+---------------------+
| user_id | time_stamp          |
+---------+---------------------+
| 3       | 2020-03-21 10:16:13 |
| 7       | 2020-01-04 13:57:59 |
| 2       | 2020-07-29 23:09:44 |
| 6       | 2020-12-09 10:39:37 |
+---------+---------------------+
Confirmations table:
+---------+---------------------+-----------+
| user_id | time_stamp          | action    |
+---------+---------------------+-----------+
| 3       | 2021-01-06 03:30:46 | timeout   |
| 3       | 2021-07-14 14:00:00 | timeout   |
| 7       | 2021-06-12 11:57:29 | confirmed |
| 7       | 2021-06-13 12:58:28 | confirmed |
| 7       | 2021-06-14 13:59:27 | confirmed |
| 2       | 2021-01-22 00:00:00 | confirmed |
| 2       | 2021-02-28 23:59:59 | timeout   |
+---------+---------------------+-----------+
Output: 
+---------+-------------------+
| user_id | confirmation_rate |
+---------+-------------------+
| 6       | 0.00              |
| 3       | 0.00              |
| 7       | 1.00              |
| 2       | 0.50              |
+---------+-------------------+
Explanation: 
User 6 did not request any confirmation messages. The confirmation rate is 0.
User 3 made 2 requests and both timed out. The confirmation rate is 0.
User 7 made 3 requests and all were confirmed. The confirmation rate is 1.
User 2 made 2 requests where one was confirmed and the other timed out. The confirmation rate is 1 / 2 = 0.5.

 

📝 [SQL/Medium] Confirmation Rate 계산하기

1. 문제 풀이의 핵심: "분자와 분모를 정확히 정의하라"

• 분모: 유저별 전체 확인 요청 횟수 (COUNT(c.user_id))
• 분자: 그중 action = 'confirmed'인 횟수
• 핵심 예외: 요청이 아예 없는 유저(분모가 0)는 승인율을 0으로 표시해야 합니다.

---

2. 가장 효율적인 정답 (AVG 활용)

AVG 함수는 조건문과 결합하면 매우 강력합니다. confirmed면 1, 아니면 0을 준 뒤 평균을 내면 그것이 바로 비율이 됩니다.

SQL

 

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

🔍 왜 이 쿼리가 정답일까요?

1. c.action = 'confirmed': 이 조건이 참이면 1, 거짓이면 0을 반환합니다.
2. AVG(...): (1+0+1)/3과 같은 방식으로 승인율을 계산합니다.
3. IFNULL(..., 0): LEFT JOIN 결과 요청 기록이 없는 유저(ID 6번 같은 경우)는 AVG 결과가 NULL이 되는데, 이를 0으로 바꿔줍니다.

---

3. 사용자님 쿼리의 오답노트 (수정할 점)

SELECT s.user_id, 
        CASE
            WHEN COUNT(c.action) = 0 THEN ROUND(COUNT(c.action),2)
            ELSE ROUND((SELECT COUNT(*) FROM Confirmations
                  WHERE action = 'Confirmed'
                  GROUP BY user_id) / COUNT(c.action), 2) 
        END AS confirmation_rate 
FROM Signups s
LEFT JOIN Confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id

❌ 서브쿼리의 위험성

ELSE ROUND((SELECT COUNT(*) FROM Confirmations ... GROUP BY user_id) / COUNT(c.action), 2) 부분에서 서브쿼리는 모든 유저의 그룹화된 결과를 반환하려 합니다. SQL은 하나의 칸(Cell)에 하나의 값만 들어가는 것을 원칙으로 하므로 "Subquery returns more than 1 row" 에러가 발생합니다.

 

✅ 수동 계산 방식(SUM 활용)으로 수정한다면:

SELECT 
    s.user_id,
    ROUND(
        IFNULL(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;

---

쿼리테스트 출제자의 의도

이 문제는 **'비율(Rate) 데이터'**를 얼마나 세련되게 추출하는지 테스트합니다.

1. 조건부 집계(Conditional Aggregation): SUM이나 AVG 내부에 조건문을 넣어 특정 상태값의 비중을 계산할 수 있는가?
2. Outer Join의 활용: 가입만 하고 요청은 안 한 유저(NULL)를 누락시키지 않고 포함할 수 있는가?
3. NULL 처리 능력: 산술 연산 결과가 NULL일 때 이를 비즈니스 로직에 맞게 0으로 치환할 수 있는가?

---

🚩 분석 팁

"비율을 구할 때는 AVG(조건문)가 마법의 열쇠다."
CASE WHEN으로 1과 0을 나누고 SUM으로 합쳐서 분모로 나누는 복잡한 과정 대신,
AVG(컬럼 = '원하는상태') 한 줄이면 깔끔하게 비율을 구할 수 있다.
AVG(조건문) 패턴은 실무에서 '구매 전환율', '클릭률(CTR)', '승인율' 등을 구할 때 매일같이 쓰이는 "치트키" 같은 문법
단, NULL 처리를 위한 IFNULL이나 COALESCE를 잊지 말 것!