[SQL/실전] 이탈 위기 고객 탐지: CTE와 윈도우 함수 활용 상태 분석 (Leetcode3716 Medium)

 

3716. Find Churn Risk Customers

Find Churn Risk Customers - LeetCode

 

1. Problem

단순히 구독을 해지한 사람이 아니라, 해지 징후가 보이는 '위험 고객'을 다음 4가지 기준으로 추출해야 한다.

• 조건 1: 현재 구독 상태가 활성 중이어야 한다 (마지막 이벤트가 cancel이 아님).
• 조건 2: 과거에 최소 한 번 이상의 downgrade 이력이 있어야 한다.
• 조건 3: 현재 플랜의 수익이 역대 최대 수익의 50% 미만이어야 한다.
• 조건 4: 첫 가입일로부터 마지막 이벤트 발생일까지의 기간이 60일 이상이어야 한다.

---

2. Solution: CTE를 활용한 시점별 데이터 모듈화

가장 첫 시점, 마지막 시점, 그리고 최대 금액 시점을 각각의 임시 테이블로 정의한 뒤 조인하여 최종 결과를 도출한다.

# 첫 구독
WITH first AS (
    SELECT f.user_id, f.event_date, f.event_type, f.plan_name, f.monthly_amount
    FROM ( SELECT user_id, event_date,event_type, plan_name, monthly_amount,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date ASC) AS first_date_num
            FROM subscription_events ) f
    WHERE f.first_date_num = 1 
),
# 마지막 구독
last AS (
    SELECT l.user_id, l.event_date, l.event_type, l.plan_name, l.monthly_amount
    FROM (  SELECT user_id, event_date,event_type, plan_name, monthly_amount,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date DESC) AS last_date_num
            FROM subscription_events) l
    WHERE l.last_date_num = 1 AND l.event_type != 'cancel'    # 조건1 마지막활동이 구독취소가 아닐 것
),
# 돈 많이 낸 구독
highest AS (
    SELECT user_id, MAX(monthly_amount) AS max_historical_amount
    FROM subscription_events 
    GROUP BY user_id
),
# 강등 이력 여부
down AS (
    SELECT user_id
    FROM subscription_events
    GROUP BY user_id
    HAVING SUM(CASE WHEN event_type = 'downgrade' THEN 1 ELSE 0 END) >= 1  # 조건2 강등된 적 있음
)

# 메인 쿼리 
SELECT d.user_id, 
       l.plan_name AS current_plan, 
       l.monthly_amount AS current_monthly_amount, 
       h.max_historical_amount,
       DATEDIFF(l.event_date, f.event_date) AS days_as_subscriber
FROM down d
JOIN first f ON d.user_id = f.user_id
JOIN last l ON d.user_id = l.user_id
JOIN highest h ON d.user_id = h.user_id
WHERE l.monthly_amount < h.max_historical_amount*0.5     # 조건3 현재 구독료가 과거 최고구독료 보다 50%이하
        AND DATEDIFF(l.event_date, f.event_date) >= 60   # 조건4 구독기간 60일 이상
ORDER BY days_as_subscriber DESC, d.user_id ASC;

 

 

3. Takeaway: 이 쿼리의 객관적 분석

• 비즈니스 로직의 분리: 복잡한 4가지 조건을 한 번에 풀려 하지 않고 first, last, highest, down이라는 네 개의 논리적 단위로 쪼개었다. 이는 쿼리의 가독성을 높일 뿐만 아니라 유지보수를 매우 쉽게 만든다.
• ROW_NUMBER()의 영리한 활용:
  • 동일한 테이블을 여러 번 참조하지만, ORDER BY를 ASC(최초), DESC(최신), amount DESC(최고가)로 다르게 주어 필요한 시점의 행만 정확히 낚아챘다.

 

 (1) HAVING MAX(event_date) 만으로는 마지막 행을 가져올 수 없다

처음에 많이 착각하는 부분이다.

# 틀린 쿼리 
GROUP BY user_id HAVING MAX(event_date)

이렇게 쓰면 마지막 이벤트가 필터링될 것처럼 보이지만, 이는 단순히 "날짜 값"만 반환할 뿐이다. MAX(event_date)는 값 하나만 반환한다.그 날짜에 해당하는 event_type, plan_name, monthly_amount는 가져오지 못한다. 즉, "마지막 날짜"는 구했지만
"마지막 행 전체"는 구하지 못한 상태가 된다.

 

해결 방법은

• MAX(event_date) 서브쿼리 + JOIN
• 또는 ROW_NUMBER() 사용
  이다.

(2) WHERE에 downgrade 조건을 넣으면 데이터가 사라진다

# 틀린 쿼리
WHERE event_type = 'downgrade'

처음에는 이렇게 쓰기 쉽다. 하지만 이렇게 하면 해당 유저의 다른 이벤트들이 전부 제거된다. 이 문제의 조건은: "과거에 downgrade가 최소 1번 있었는가?"

GROUP BY user_id
HAVING SUM(CASE WHEN event_type = 'downgrade' THEN 1 ELSE 0 END) >= 1

HAVING은 그룹 결과에 대한 조건이기 때문에 맞는 위치이다. 즉, 특정 행을 필터링하는 것이 아니라 유저 단위 집계 조건이다. 반드시 이렇게 써야 한다. 

위치	무엇을  필터링?
WHERE	개별 행(row)
HAVING	그룹 집계 결과

downgrade “이력이 있었는지”는 유저 단위 집계 결과니까 HAVING이 맞다.

 

 

(3) 현재 요금은 GROUP BY로 구할 수 없다

많이 실수하는 포인트이다.

# 틀린 쿼리
MAX(monthly_amount)

이건 과거 최대 요금이다. 하지만 우리가 필요한 것은: 현재 요금 (마지막 이벤트 기준)

현재 요금은 집계값이 아니다. 특정 행의 값이다. 따라서 반드시 마지막 이벤트를 먼저 구하고 그 행에서 monthly_amount를 가져와야 한다.

# 마지막 구독
last AS (
    SELECT l.user_id, l.event_date, l.event_type, l.plan_name, l.monthly_amount
    FROM (  SELECT user_id, event_date,event_type, plan_name, monthly_amount,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date DESC) AS last_date_num
            FROM subscription_events) l
)

 

 (4) cancel 이벤트를 고려하지 않으면 조건이 자동 통과된다

문제에서 cancel 이벤트의 monthly_amount는 0이다. 만약 마지막 이벤트가 cancel인데 이를 걸러내지 않으면:

 

WHERE current_amount < max_amount * 0.5

는 자동으로 TRUE가 된다. 0은 항상 최대값의 50%보다 작기 때문이다. 즉, cancel 유저가 잘못 포함될 위험이 있다. 반드시 마지막 이벤트가 cancel이 아닌지 확인해야 한다.

# 마지막 구독
last AS (
    SELECT l.user_id, l.event_date, l.event_type, l.plan_name, l.monthly_amount
    FROM (  SELECT user_id, event_date,event_type, plan_name, monthly_amount,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date DESC) AS last_date_num
            FROM subscription_events) l
    WHERE l.last_date_num = 1 AND l.event_type != 'cancel'    # 조건1 마지막활동이 구독취소가 아닐 것
),

 

(5) 최고 요금은 굳이 ROW_NUMBER가 필요 없다

처음에는 이렇게 접근하기 쉽다.

# 틀린 쿼리
ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY monthly_amount DESC )

 

하지만 이 문제에서 필요한 것은 "최고 요금이었던 행”이 아니라 최고 요금 값 자체 단일값이다. 따라서 더 간단하게 해결할 수 있다. 불필요하게 윈도우 함수를 쓰면 쿼리가 복잡해지고 가독성이 떨어진다.

MAX(monthly_amount)

# 돈 많이 낸 구독
highest AS (
    SELECT user_id, MAX(monthly_amount) AS max_historical_amount
    FROM subscription_events 
    GROUP BY user_id
)

 

 

💡 팁

• 실무적 제언: 이 분석은 SaaS(구독 모델) 기업에서 매우 핵심적이다. 이미 해지한 뒤에 붙잡는 것은 비용이 많이 들지만, 이 쿼리로 추출된 '위험군'에게 맞춤형 혜택을 제공하면 훨씬 저렴한 비용으로 고객을 유지(Retention)할 수 있다.