SQL GrowthSQL로 전환 여부 파악하기
오늘 푸는 문제 소개
오늘 다룰 문제는
"클릭은 있지만 구매는 없는 셀러 찾기"
입니다.
왜 이런 분석이 필요할까요?
1. 셀러 성과 관리
- 어떤 셀러의 상품은 관심을 받지만 구매로 이어지지 않는지 파악
- 상품 페이지의 문제점(가격, 상세 설명, 리뷰 등)을 개선할 대상 선정
2. 플랫폼 최적화
- 전환율이 낮은 셀러들의 공통점 분석
- UI/UX 개선이 필요한 영역 파악
3. 비즈니스 의사결정
- 마케팅 예산을 어디에 집중할지 결정
- 셀러 교육 프로그램 대상 선정
💡 실제로 쿠팡, 11번가, 네이버 쇼핑 같은 플랫폼에서는 이런 분석을 통해 셀러들에게 인사이트를 제공하고, 전체 플랫폼의 전환율을 높이는 전략을 수립합니다.
문제 상황
당신은 이커머스 플랫폼의 데이터 분석가입니다. 운영팀에서 다음과 같은 요청을 받았습니다:
"사용자들이 상품을 클릭했지만 구매로 이어지지 않은 셀러들을 찾아주세요. 이들에게 전환율 개선 교육을 제공하려고 합니다."
주어진 데이터
user_events
- 사용자의 행동 기록
| 컬럼명 | 설명 |
|---|---|
| event_id | 이벤트 고유 ID |
| session_id | 세션 ID |
| user_id | 사용자 ID |
| seller_id | 셀러 ID |
| event_type | 이벤트 종류 (viewed, clicked, converted) |
| product_id | 상품 ID |
| created_at | 발생 시간 |
--
sellers
- 셀러 정보
| 컬럼명 | 설명 |
|---|---|
| seller_id | 셀러 ID |
| seller_name | 셀러 이름 |
| category | 카테고리 |
요구사항
최소 1개 이상의 'clicked' 이벤트가 있지만 'converted' 이벤트가 0개인 모든 셀러를 찾아서, 셀러 이름을 알파벳 순으로 정렬하여 반환하세요.
문제를 풀기 전에 알아야 할 개념
이 문제를 해결하기 위해 필요한 SQL 개념들을 먼저 정리해볼까요?
1. JOIN - 테이블 연결하기
두 개 이상의 테이블에서 관련된 데이터를 합쳐서 조회할 때 사용합니다.
SELECT * FROM sellers s INNER JOIN user_events e ON s.seller_id = e.seller_id;
왜 JOIN이 필요한가?
- user_events 테이블에는 seller_id만 있고 셀러 이름이 없음
- sellers 테이블에 셀러 이름이 저장되어 있음
- 두 테이블을 연결해야 셀러 이름을 출력할 수 있음
2. GROUP BY - 그룹별로 묶기
특정 컬럼을 기준으로 데이터를 그룹화할 때 사용합니다.
SELECT seller_id, COUNT(*) as event_count FROM user_events GROUP BY seller_id;
이 문제에서는?
- 셀러별로 이벤트를 묶어서
- 각 셀러가 받은 클릭 수와 전환 수를 계산해야 함
3. HAVING - 그룹에 조건 걸기
GROUP BY로 묶인 결과에 조건을 적용할 때 사용합니다. WHERE와 비슷하지만, 집계 함수 결과에 조건을 걸 수 있다는 차이가 있습니다.
SELECT seller_id, COUNT(*) as event_count FROM user_events GROUP BY seller_id HAVING COUNT(*) > 5;
WHERE vs HAVING
| WHERE | HAVING |
|---|---|
| 개별 행에 조건 적용 | 그룹화된 결과에 조건 적용 |
| 그룹화 전 필터링 | 그룹화 후 필터링 |
| 집계 함수 사용 불가 | 집계 함수 사용 가능 |
4. CASE WHEN - 조건부 값 반환
조건에 따라 다른 값을 반환할 때 사용합니다. 마치 프로그래밍의 if-else문과 같습니다.
SELECT event_type, CASE WHEN event_type = 'clicked' THEN 1 ELSE 0 END as is_click FROM user_events;
이 문제에서는?
- 'clicked' 이벤트만 카운트하고
- 'converted' 이벤트만 따로 카운트해야 함
- CASE WHEN을 SUM 안에 넣어서 조건부 집계 가능
문제 풀이 접근법
Step 1: 문제 분석하기
"클릭은 있지만 구매는 없는 셀러"를 찾으려면:
- ✅ 셀러별로 이벤트를 그룹화
- ✅ 각 셀러의 clicked 이벤트 개수 계산
- ✅ 각 셀러의 converted 이벤트 개수 계산
- ✅ clicked > 0 AND converted = 0인 셀러만 필터링
Step 2: 필요한 데이터 파악하기
- sellers 테이블에서: seller_name (출력 대상)
- user_events 테이블에서: event_type (집계 대상)
- 두 테이블을 seller_id로 연결
Step 3: 쿼리 구조 설계하기
- FROM과 JOIN으로 두 테이블 연결
- GROUP BY로 셀러별로 묶기
- HAVING으로 조건 필터링
- clicked 이벤트 개수 > 0
- converted 이벤트 개수 = 0
- ORDER BY로 정렬
Step 4: 조건부 집계 구현하기
특정 이벤트 타입만 카운트하는 방법:
SUM(CASE WHEN event_type = 'clicked' THEN 1 ELSE 0 END)
💡 이 패턴은 실무에서 정말 자주 사용됩니다! 조건에 따라 다르게 집계할 때 필수적인 기법입니다.
실습해보기
이제 위 개념들을 활용해서 문제를 풀어보세요.
💡 힌트
- 셀러 이름을 가져오기 위해 두 테이블을 조인해야 합니다
- GROUP BY를 사용하여 셀러별로 이벤트를 집계하세요
- HAVING 절로 집계 조건에 따라 그룹을 필터링할 수 있습니다
- SUM 안에 CASE WHEN을 사용하여 특정 이벤트 타입을 카운트하세요
약간의 시도
SELECT seller_id FROM user_events GROUP BY seller_id HAVING COUNT(CASE WHEN event_type = 'clicked' THEN 1 END) >= 1 AND COUNT(CASE WHEN event_type = 'converted' THEN 1 END) = 0
또는 SUM 을 이용해서 구할 수 있어요.
SELECT seller_id, FROM user_events GROUP BY seller_id HAVING SUM(event_type = 'clicked') >= 1 AND SUM(event_type = 'converted') = 0
boolean 표현식이 1(true) 또는 0(false)으로 평가되기 때문에 SUM으로 조건에 맞는 행의 개수를 셀 수 있기 때문이에요.
이 문제를 통해 배울 수 있는 것
- ✅ 실무에서 가장 많이 사용하는 JOIN + GROUP BY + HAVING 패턴
- ✅ 조건부 집계를 위한 CASE WHEN 활용법
- ✅ 전환 퍼널 분석의 기본 개념
- ✅ 복잡한 비즈니스 요구사항을 SQL로 구현하는 방법
다음 단계
이 문제를 풀었다면, 다음과 같은 심화 문제에도 도전해보세요:
- 📊 셀러별 클릭-전환율(Conversion Rate) 계산하기
- 📈 전환율이 가장 높은/낮은 카테고리 찾기
- ⏰ 시간대별 전환율 분석하기
- 🔍 코호트 분석으로 셀러 성과 추적하기
SQL 실력은 많은 문제를 풀어보면서 늘어납니다.
위 문제를 더 풀어보고 싶으시다면 문제 링크를 클릭해주세요.
더 많은 실전 문제를 풀어보세요! 🚀
