[SQL/오답] 집계 함수와 CASE문 순서에 따른 영향 (LeetCode3497 Medium)

 

1. Problem

구독 서비스의 사용자 행동 패턴을 분석하기 위해 '무료 체험에서 유료 결제로 전환한 유저'를 식별하고, 각 기간의 평균 활동 시간을 계산해야 한다. 이때 한 사용자의 데이터가 여러 행(Row)으로 나뉘어 있으므로, 이를 사용자별로 그룹화하여 하나의 행으로 출력하는 것이 핵심이다.

2. Solution

사용자별로 그룹화(GROUP BY user_id)한 상태에서 특정 조건의 평균만 구하기 위해 AVG(CASE WHEN...) 패턴을 사용한다.

• 필터링 로직: HAVING 절에서 MAX(조건식) 테크닉을 사용하여 두 상태를 모두 경험한 '전환자'만 골라낸다.

3. Takeaway (Why I Failed)

처음에는 CASE WHEN 안에 AVG를 넣는 실수를 했다. 이 과정을 통해 SQL의 작동 원리 두 가지를 명확히 정립한다.

• 집계 함수의 NULL 무시: AVG 함수는 NULL 값을 계산 대상에서 제외한다. CASE문을 AVG 내부에 작성하면 조건에 맞지 않는 행을 NULL로 만들어, 원하는 그룹의 데이터만 정확히 평균을 낼 수 있다.
• 행 단위 연산 vs 그룹 단위 연산: CASE가 밖에 있으면 각 행 단위로 조건을 판단하려 하므로, 그룹화된 데이터의 특성을 제대로 반영하지 못한다. **"필터를 먼저 적용(CASE)하고 계산(AVG)한다"**는 순서를 지키는 것이 중요하다.

Analyze Subscription Conversion - LeetCode

https://leetcode.com/problems/analyze-subscription-conversion/description/

 

문제 

Table: UserActivity

+------------------+---------+
| Column Name      | Type    | 
+------------------+---------+
| user_id          | int     |
| activity_date    | date    |
| activity_type    | varchar |
| activity_duration| int     |
+------------------+---------+
(user_id, activity_date, activity_type) is the unique key for this table.
activity_type is one of ('free_trial', 'paid', 'cancelled').
activity_duration is the number of minutes the user spent on the platform that day.
Each row represents a user's activity on a specific date.
A subscription service wants to analyze user behavior patterns. The company offers a 7-day free trial, after which users can subscribe to a paid plan or cancel. Write a solution to:

Find users who converted from free trial to paid subscription
Calculate each user's average daily activity duration during their free trial period (rounded to 2 decimal places)
Calculate each user's average daily activity duration during their paid subscription period (rounded to 2 decimal places)
Return the result table ordered by user_id in ascending order.

The result format is in the following example.

 

Example:

Input:

UserActivity table:

+---------+---------------+---------------+-------------------+
| user_id | activity_date | activity_type | activity_duration |
+---------+---------------+---------------+-------------------+
| 1       | 2023-01-01    | free_trial    | 45                |
| 1       | 2023-01-02    | free_trial    | 30                |
| 1       | 2023-01-05    | free_trial    | 60                |
| 1       | 2023-01-10    | paid          | 75                |
| 1       | 2023-01-12    | paid          | 90                |
| 1       | 2023-01-15    | paid          | 65                |
| 2       | 2023-02-01    | free_trial    | 55                |
| 2       | 2023-02-03    | free_trial    | 25                |
| 2       | 2023-02-07    | free_trial    | 50                |
| 2       | 2023-02-10    | cancelled     | 0                 |
| 3       | 2023-03-05    | free_trial    | 70                |
| 3       | 2023-03-06    | free_trial    | 60                |
| 3       | 2023-03-08    | free_trial    | 80                |
| 3       | 2023-03-12    | paid          | 50                |
| 3       | 2023-03-15    | paid          | 55                |
| 3       | 2023-03-20    | paid          | 85                |
| 4       | 2023-04-01    | free_trial    | 40                |
| 4       | 2023-04-03    | free_trial    | 35                |
| 4       | 2023-04-05    | paid          | 45                |
| 4       | 2023-04-07    | cancelled     | 0                 |
+---------+---------------+---------------+-------------------+
Output:

+---------+--------------------+-------------------+
| user_id | trial_avg_duration | paid_avg_duration |
+---------+--------------------+-------------------+
| 1       | 45.00              | 76.67             |
| 3       | 70.00              | 63.33             |
| 4       | 37.50              | 45.00             |
+---------+--------------------+-------------------+
Explanation:

User 1:
Had 3 days of free trial with durations of 45, 30, and 60 minutes.
Average trial duration: (45 + 30 + 60) / 3 = 45.00 minutes.
Had 3 days of paid subscription with durations of 75, 90, and 65 minutes.
Average paid duration: (75 + 90 + 65) / 3 = 76.67 minutes.
User 2:
Had 3 days of free trial with durations of 55, 25, and 50 minutes.
Average trial duration: (55 + 25 + 50) / 3 = 43.33 minutes.
Did not convert to a paid subscription (only had free_trial and cancelled activities).
Not included in the output because they didn't convert to paid.
User 3:
Had 3 days of free trial with durations of 70, 60, and 80 minutes.
Average trial duration: (70 + 60 + 80) / 3 = 70.00 minutes.
Had 3 days of paid subscription with durations of 50, 55, and 85 minutes.
Average paid duration: (50 + 55 + 85) / 3 = 63.33 minutes.
User 4:
Had 2 days of free trial with durations of 40 and 35 minutes.
Average trial duration: (40 + 35) / 2 = 37.50 minutes.
Had 1 day of paid subscription with duration of 45 minutes before cancelling.
Average paid duration: 45.00 minutes.
The result table only includes users who converted from free trial to paid subscription (users 1, 3, and 4), and is ordered by user_id in ascending order.

 

SELECT 
    user_id, 
    ROUND(AVG(CASE WHEN activity_type = 'free_trial' THEN activity_duration END), 2) AS trial_avg_duration,
    ROUND(AVG(CASE WHEN activity_type = 'paid' THEN activity_duration END), 2) AS paid_avg_duration
FROM UserActivity 
GROUP BY user_id
HAVING 
    MAX(activity_type = 'free_trial') = 1 AND 
    MAX(activity_type = 'paid') = 1
ORDER BY user_id;

쿼리 핵심 포인트

1. AVG(CASE WHEN ...): 특정 조건(activity_type = 'free_trial')에 맞는 데이터만 골라내어 평균을 낸다. 조건에 맞지 않으면 NULL이 되고, AVG 함수는 NULL을 무시하고 계산하므로 정확한 평균값이 나온다.
2. GROUP BY user_id: 이제 사용자를 type으로 나누지 않고 한 명당 한 줄로 묶습니다.
3. 전환자(Converter) 필터링: HAVING 절에서 해당 사용자가 두 가지 타입을 모두 경험했는지 확인합니다. (MAX(조건)은 해당 조건이 하나라도 존재하면 1을 반환하는 테크닉입니다.)

CASE WHEN activity_type = 'free_trial' THEN ROUND(AVG(activity_duration),2) END AS trial_avg_duration,
CASE WHEN activity_type = 'paid' THEN ROUND(AVG(activity_duration),2) END AS paid_avg_duration

 

• 오답 쿼리: CASE WHEN type = 'A' THEN AVG(duration) END
• 해석: (전체 평균을 낸 후) "만약 타입이 A라면, 이미 나온 평균값을 보여줘."

ROUND(AVG(CASE WHEN activity_type = 'free_trial' THEN activity_duration END), 2) AS trial_avg_duration,
ROUND(AVG(CASE WHEN activity_type = 'paid' THEN activity_duration END), 2) AS paid_avg_duration

• 정답 쿼리: AVG(CASE WHEN type = 'A' THEN duration END)
• 해석: "타입이 A인 데이터들만 골라서, 그것들만 가지고 평균을 내줘."

---

왜 AVG가 밖으로 나가야 할까? 

①: SQL은 '행' 단위로 판단

CASE가 밖에 있으면, 컴퓨터는 **각 행(Row)**마다 이렇게 물어본다.
"이 행은 free_trial이야?" -> "응" -> "그럼 이 사용자의 전체 평균을 보여줘.""이 행은 paid야?" -> "응" -> "그럼 이 사용자의 전체 평균을 보여줘." 결국 무료 체험 기간의 데이터든 유료 기간의 데이터든, 똑같은 전체 평균값만 반복해서 보여주게 된다. (우리는 무료 따로, 유료 따로 평균 을 내고 싶은데!)

 

이유 ②: NULL을 활용하기 위해.

정답 쿼리(AVG가 밖)의 작동 방식은 이렇다.

CASE WHEN type = 'free_trial' THEN duration END

• 이 필터는 free_trial이 아닌 데이터는 전부 NULL로 만들어버린다.

AVG(...)

• 중요한 사실: SQL의 모든 집계 함수(AVG, SUM, COUNT)는 NULL을 계산에서 아예 빼버린다.
• 결과적으로 AVG 계산기 안에는 free_trial인 데이터들만 들어가게 되고, 나머지 쓰레기 값(NULL)은 무시되므로 우리가 원하는 정확한 '무료 체험 평균'이 나온다.

---

전체 오답 쿼리

# 오답 쿼리
SELECT user_id, 
    CASE WHEN activity_type = 'free_trial' THEN ROUND(AVG(activity_duration),2) END AS trial_avg_duration,
    CASE WHEN activity_type = 'paid' THEN ROUND(AVG(activity_duration),2) END AS paid_avg_duration 
FROM UserActivity 
GROUP BY user_id, activity_type
HAVING trial_avg_duration IS NOT NULL AND paid_avg_duration IS NOT NULL
ORDER BY user_id;