[SQL] 코호트 분석이란?, 쿼리테스트 출제 유형, 관련 함수

---

1. 코호트 분석(Cohort Analysis)이란?

**코호트(Cohort)**란 '특정 기간 동안 공통된 특성이나 경험을 공유하는 사용자 집단'을 의미합니다.

• 쉽게 말하면: "사람들을 **들어온 시기(가입월 등)**별로 묶어서, 시간이 흐름에 따라 이들이 어떻게 행동하는지(구매, 유지, 이탈) 관찰하는 것"입니다.
• 목적: 서비스가 개선되고 있는지, 특정 시기에 들어온 유저들이 유독 충성도가 높은지 등을 파악하기 위함입니다.

---

2. SQL 쿼리 테스트 출제 유형

기업에서는 주로 다음과 같은 3가지 유형으로 출제합니다.

유형	핵심 내용	난이도
유지율(Retention)	가입 후 1개월 뒤, 2개월 뒤에도 남아있는 유저 비중 구하기	상
재구매율	첫 구매 이후 특정 기간 내에 다시 구매한 유저 비율	중상
누적 매출(LTV)	특정 시기에 가입한 유저들이 현재까지 발생시킨 총 매출액	상

• 자주 나오는 문제 패턴:
  • "202X년 X월 가입자 중, 다음 달에도 구매 기록이 있는 유저의 비율을 구하시오."
  • "가입 월별로 1개월차, 2개월차... N개월차 리텐션을 구하시오."

---

3. 코호트 분석을 위해 꼭 알아야 하는 SQL 함수 & 문법

코호트 분석은 시간의 흐름을 다루기 때문에 날짜 관련 함수가 필수입니다.

1. 날짜 추출 함수: YEAR(), MONTH(), DATE_FORMAT(date, '%Y-%m')
   • 유저를 '월별'로 그룹화할 때 사용합니다.
2. 날짜 차이 계산: DATEDIFF(date1, date2), TIMESTAMPDIFF(MONTH, date1, date2)
   • 가입일로부터 구매일까지 몇 달이 지났는지(1개월차, 2개월차 등) 계산할 때 필수입니다.
3. 중복 제거 카운트: COUNT(DISTINCT USER_ID)
   • 한 명의 유저가 여러 번 구매해도 '인원수'는 1명으로 세어야 하므로 가장 중요합니다.
4. 조건부 집계: CASE WHEN
   • 피벗 테이블(가로로 긴 표) 형태로 리텐션을 출력할 때 사용합니다.

---

4. 관련 핵심 개념 (함께 공부하면 좋은 것들)

• 리텐션(Retention): 사용자가 서비스를 다시 이용하는 비율. 서비스 생존의 척도입니다.
• 리텐션 커브(Retention Curve): 시간이 지남에 따라 유저가 빠져나가는 모습을 그래프로 그린 것. 완만하게 평행을 유지해야 좋은 서비스입니다.
• 세그먼트(Segment): 코호트가 '시간' 기준이라면, 세그먼트는 '성별', '나이', '유입 경로' 등 유저의 '특성'으로 나누는 것을 말합니다.

---

🚩 "분석가는 왜 코호트에 집착하는가?"

"전체 평균은 거짓말을 하기 때문이다."

전체 매출이 올랐어도, 새로 들어온 유저만 많고 기존 유저가 다 빠져나가고 있다면 그 서비스는 위험합니다. 코호트 분석은 **기존 유저가 얼마나 잘 유지되는지(Stickiness)**를 보여주는 가장 정직한 지표입니다. "분모(가입 시점)를 고정하고 분자(이후 행동)를 추적한다"는 원리만 기억하면 됩니다.