[SQL/오답] 코호트 분석: 고정 분모와 동적 분자를 활용한 구매 비율 산출 (프로그래머스 Lv5)

[SQL/Level 5] 상품을 구매한 회원 비율 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/276035

1. Problem

2021년에 가입한 전체 회원들 중, 각 월별로 상품을 구매한 회원의 비율을 구해야 한다. 비율은 (당월 구매한 2021년 가입 회원 수 / 2021년 전체 가입 회원 수)로 계산하며, 소수점 두 번째 자리에서 반올림한다. 특정 시점에 유입된 집단의 유지율(Retention)을 추적하는 전형적인 비즈니스 분석 문제이다.

2. Solution

전체 모수(분모)를 서브쿼리로 고정하고, 월별 구매자(분자)를 집계하여 결합하는 전략을 사용한다.

• 분모의 고정: (SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = 2021) 서브쿼리는 전체 쿼리 실행 동안 단 하나의 상수값으로 작동하여 계산의 기준점이 된다.
• 분자의 동적 집계: COUNT(DISTINCT S.USER_ID)를 통해 각 월별로 실제 구매를 일으킨 '유니크 유저'를 카운트한다.

3. Takeaway (비즈니스 임팩트와 통계적 무결성)

가장 높은 난이도의 문제를 해결하며 분석가가 확보해야 할 객관적 통찰을 정리한다.

• 코호트(Cohort) 분석의 이해: 특정 기간에 가입한 유저 집단을 시간에 따라 추적하는 방식은 마케팅 성과 측정 및 서비스 이탈 분석의 핵심이다. 이번 문제를 통해 '가입 시점'이라는 기준점을 잡는 훈련이 완결되었다.
• 상수와 변수의 구분: 전체 회원 수라는 '정적 데이터'와 월별 구매자라는 '동적 데이터'를 하나의 식에 결합하는 능력은 복잡한 비즈니스 지표를 설계하는 기초가 된다.
• 데이터 무결성 검증: 만약 DISTINCT를 누락한다면 한 명의 유저가 여러 번 구매했을 때 비율이 100%를 초과하는 심각한 논리 오류가 발생한다. 지표 산출 시 중복 제거는 선택이 아닌 필수이다.

 

1. 데이터 테이블 구조

👤 회원 정보 (USER_INFO)

Column name	Type	Description
USER_ID	INTEGER	회원 ID
JOINED	DATE	가입일 (2021년 가입자 필터링 기준)

📈 판매 정보 (ONLINE_SALE)

Column name	Type	Description
USER_ID	INTEGER	회원 ID (구매 여부 확인)
SALES_DATE	DATE	판매일 (년, 월 추출 기준)

---

2. 문제 풀이의 핵심 3단계

이 문제는 **"기준점(2021년 가입자)"**을 먼저 명확히 잡는 것이 핵심입니다.

① 분모 구하기 (2021년 가입한 전체 회원 수)

2021년에 가입한 유저의 총합은 어떤 월을 계산하든 변하지 않는 고정된 값입니다. 따라서 서브쿼리나 변수로 미리 뽑아두어야 합니다.

SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = 2021

② 분자 구하기 (월별 구매 회원 수)

여기서 주의할 점은 **'구매 건수'가 아니라 '구매 회원 수'**라는 점입니다. 한 회원이 한 달에 여러 번 구매했어도 1명으로 카운트해야 하므로 DISTINCT가 필수입니다.

• 조건: USER_INFO에서 2021년 가입자만 조인해서 가져와야 함.

③ 비율 계산 및 포맷팅

• 비율: 분자 / 분모
• 반올림: ROUND(비율, 1) (소수점 두 번째 자리에서 반올림하여 첫 번째 자리까지 표시)

정답쿼리

-- 2021년 가입자 총 인원수를 서브쿼리로 활용
SELECT 
    YEAR(S.SALES_DATE) AS YEAR,
    MONTH(S.SALES_DATE) AS MONTH,
    COUNT(DISTINCT S.USER_ID) AS PURCHASED_USERS,
    ROUND(COUNT(DISTINCT S.USER_ID) / (SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = 2021), 1) AS PURCHASED_RATIO
FROM ONLINE_SALE S
JOIN USER_INFO U ON S.USER_ID = U.USER_ID
WHERE YEAR(U.JOINED) = 2021
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;

쿼리테스트 출제자의 의도

이 문제는 단순히 쿼리 문법을 아는 것을 넘어 비즈니스 데이터의 구조를 이해하는지 테스트합니다.

① 고정된 기준(Denominator)과 변하는 집계(Numerator) 구분

대부분의 초보자는 전체 회원 수를 어떻게 처리할지 몰라 당황합니다. 출제자는 **"상수값(전체 인원)을 서브쿼리로 분리하여 계산식에 녹여낼 수 있는가?"**를 봅니다.

• 의도: 통계적 분석 역량 확인

② 중복 제거의 중요성 파악 (DISTINCT)

실무 데이터에서 '방문 수'와 '방문자 수'는 엄연히 다릅니다. 이 문제에서 DISTINCT를 빠뜨리면 비율이 1(100%)을 넘게 됩니다.

• 의도: 데이터 무결성 및 정확도 검증

③ 코호트(Cohort) 분석 역량

특정 시점(2021년 가입)에 유입된 집단이 이후 시간에 따라 어떤 행동(구매)을 보이는지 분석하는 능력은 마케팅 효율 측정의 핵심입니다.

• 의도: 실제 비즈니스 임팩트 분석 능력 평가