제로베이스 데이터사이언스 스쿨 - Part.02 데이터 분석을 위한 SQL-18

12. 기출 예상 코딩 테스트

 

SQL 실전 대비 — 기출 예상 코딩 테스트 5 정리

 

1. 2가지 이상 product_name 페이지를 본 사용자 수

문제:
VIEW 테이블을 활용해 서로 다른 product_name을 2개 이상 조회한 user_id의 수(USER_CNT)를 구하시오.

 

WITH BASE AS (
  SELECT USER_ID,
         COUNT(DISTINCT PRODUCT_NAME) AS PRODUCT_NAME_CNT
    FROM VIEW
   GROUP BY USER_ID
)
SELECT COUNT(DISTINCT USER_ID) AS USER_CNT
  FROM BASE
 WHERE PRODUCT_NAME_CNT >= 2;

 

핵심 포인트

• GROUP_CONCAT 또는 COUNT(DISTINCT)로 다중 상품 조회 여부 판단
• 단순 페이지 수가 아닌 “상품 다양도(Product Diversity)”를 측정하는 로직
• 실무에서는 추천 알고리즘 또는 고객 탐색 행동(Exploration behavior) 분석에 응용 가능

---

2. 상품별 주요 페이지별 세션 수 요약

문제:
VIEW 테이블에서 상품별로 페이지(product, shopping cart, payment, payment completed) 별 세션 수를 구하시오.
단, shopping cart 세션이 0이면 제외하며, product_name 기준 오름차순 정렬한다.

 

SELECT PRODUCT_NAME,
       COUNT(DISTINCT CASE WHEN PAGE = 'product' THEN SESSION_ID END) AS PRODUCT_SESSION,
       COUNT(DISTINCT CASE WHEN PAGE = 'shopping cart' THEN SESSION_ID END) AS SHOPPING_CART_SESSION,
       COUNT(DISTINCT CASE WHEN PAGE = 'payment' THEN SESSION_ID END) AS PAYMENT_SESSION,
       COUNT(DISTINCT CASE WHEN PAGE = 'payment completed' THEN SESSION_ID END) AS PAYMENT_COMPLETED_SESSION
  FROM VIEW
 GROUP BY PRODUCT_NAME
HAVING COUNT(DISTINCT CASE WHEN PAGE = 'shopping cart' THEN SESSION_ID END) > 0
 ORDER BY PRODUCT_NAME ASC;

 

핵심 포인트

• 세션 단위 행동 로그를 페이지별 퍼널(Funnel) 형태로 구조화
• CASE WHEN + COUNT(DISTINCT SESSION_ID) 패턴으로 중복 세션 제거
• HAVING을 활용한 활성(장바구니 진입 이상) 유저만 필터링

👉 shopping cart → payment → payment completed 흐름은 구매 전환율(Conversion Funnel) 분석의 핵심이다

12-6. 기출 예상 코딩 테스트 5

.

---

3. VISIT에는 있고 VIEW에는 없는 사용자 수 비교

문제:
VISIT에는 이력이 있으나 VIEW에는 없는 사용자 수(NO_VIEW_USER_CNT)와,
반대로 VIEW에 이력이 존재하는 사용자 수(VIEW_USER_CNT)를 구하시오.

 

SELECT COUNT(DISTINCT CASE WHEN VIEW.USER_ID IS NULL THEN VISIT.USER_ID END) AS NO_VIEW_USER_CNT,
       COUNT(DISTINCT CASE WHEN VIEW.USER_ID IS NOT NULL THEN VISIT.USER_ID END) AS VIEW_USER_CNT
  FROM VISIT
  LEFT JOIN VIEW ON VISIT.USER_ID = VIEW.USER_ID;

 

핵심 포인트

• LEFT JOIN + CASE WHEN으로 존재 유무 비교
• 이탈 고객 or 단일 방문자 추적용 로직
• 웹 로그 품질 점검(로그 수집 누락 여부)에도 유용

---

4. VISIT + VIEW 통합 후 이벤트별 유저 수 분석

문제:
두 테이블(VISIT, VIEW)을 UNION ALL로 합쳐,
event_type, page별로 유저 수(USER_CNT)를 구하시오.
결과는 event_type, page 모두 내림차순 정렬한다.

WITH UNION_TABLE AS (
  SELECT EVENT_TYPE, USER_ID, PAGE FROM VISIT
  UNION ALL
  SELECT EVENT_TYPE, USER_ID, PAGE FROM VIEW
)
SELECT EVENT_TYPE, PAGE,
       COUNT(DISTINCT USER_ID) AS USER_CNT
  FROM UNION_TABLE
 GROUP BY EVENT_TYPE, PAGE
 ORDER BY EVENT_TYPE DESC, PAGE DESC;

 

핵심 포인트

• UNION ALL을 통해 다중 로그 원본 통합(ETL-like 처리)
• GROUP BY + DISTINCT COUNT로 유저 행동 빈도 집계
• 최종 결과는 이벤트-페이지 단위 행동 히트맵(Heatmap) 형태

 

* 이 글은 제로베이스 데이터사이언스 파트타임 스쿨의 강의 자료 일부를 발췌하여 작성되었습니다.

---

 

💡 생각 정리

이번 실습은 단순 SELECT 문법을 넘어, 웹 로그 데이터 분석의 사고방식을 SQL로 구현하는 훈련이었다.

특히 다음 세 가지 포인트가 인상 깊었다.

1. session_id를 기준으로 사용자 행동을 “시간적 단위 세션(Sessionization)”으로 묶는 방식
2. CASE WHEN을 이용한 퍼널 구조 집계(Funnel Aggregation)
3. UNION ALL로 로그 테이블을 병합하고, 행동별 유저수를 비교하는 통합 로그 분석 기법

이를 통해 단순 데이터 조회가 아니라 “사용자 행동의 흐름과 단계별 이탈 원인”을 SQL로 구조화할 수 있다는 점을 배웠다.

---

🚀 적용점

• 마케팅 분석
  • VIEW 기반의 퍼널 분석 결과를 전환율 계산 로직(장바구니 → 결제 완료)에 바로 적용
• 웹 로그 품질 관리
  • VISIT 대비 VIEW 누락 사용자 비율을 주기적으로 모니터링
• BI 대시보드 구현
  • UNION_TABLE 결과를 기준으로 event_type별 사용자 행동 흐름 시각화
• 세션 기반 사용자 인사이트
  • 특정 product_name별 세션당 페이지 수, 체류시간 등을 확장 분석

👉 이번 테스트는 SQL을 활용해 행동 로그를 데이터 마트처럼 설계하고 인사이트로 연결하는 고급 사고력을 요구한 실무형 문제였다.