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

 12. 기출 예상 코딩 테스트

 

SQL 실전 대비 — 기출 예상 코딩 테스트 3

 

1. 특정 서비스 다중 구매 회원 수

SELECT COUNT(DISTINCT A.MEM_NO) AS MEM_NO_CNT
FROM CUSTOMER AS A
LEFT JOIN SALE AS B ON A.MEM_NO = B.MEM_NO
LEFT JOIN SALE_DETAIL AS C ON B.ORDER_NO = C.ORDER_NO
LEFT JOIN SERVICE AS D ON C.SERVICE_CODE = D.SERVICE_CODE
WHERE SERVICE_NAME = 'lounge & bar'
  AND QTY >= 2;

• 특정 서비스(lounge & bar)를 2회 이상 구매한 회원 수
• DISTINCT + 조건 필터로 충성 고객군 추출

---

2. 연도별 가입 회원 수 비교

SELECT '2023가입회원수' AS SEGMENT, COUNT(DISTINCT MEM_NO) AS MEM_NO_CNT
FROM CUSTOMER WHERE YEAR(JOIN_DATE) = 2023
UNION ALL
SELECT '2024가입회원수', COUNT(DISTINCT MEM_NO)
FROM CUSTOMER WHERE YEAR(JOIN_DATE) = 2024
ORDER BY 1;

• UNION ALL을 활용한 세그먼트별 비교형 결과 구성
• 리포트용 데이터 구조 설계에 자주 사용됨

---

3. 서비스별 가격 요약 및 편차

 

SELECT SERVICE_NAME,
       SUM(PRICE) AS PRICE_SUM,
       MAX(PRICE) AS PRICE_MAX,
       MIN(PRICE) AS PRICE_MIN,
       MAX(PRICE) - MIN(PRICE) AS PRICE_DIFF
FROM SERVICE
GROUP BY SERVICE_NAME
HAVING MAX(PRICE) - MIN(PRICE) <> 0
ORDER BY SERVICE_NAME ASC;

• 가격 합계·최대·최소·편차까지 한번에 계산
• HAVING으로 동일가 서비스 제거

---

4. 회원별 구매횟수 구간 분류

 

WITH BASE AS (
  SELECT A.MEM_NO, COUNT(DISTINCT B.ORDER_NO) AS ORDER_CNT
  FROM CUSTOMER AS A
  LEFT JOIN SALE AS B ON A.MEM_NO = B.MEM_NO
  GROUP BY A.MEM_NO
)
SELECT CASE WHEN ORDER_CNT = 0 THEN '0회'
            WHEN ORDER_CNT = 1 THEN '1회'
            WHEN ORDER_CNT >= 2 THEN '2회이상' END AS SEGMENT,
       COUNT(DISTINCT MEM_NO) AS MEM_NO_CNT
FROM BASE
GROUP BY 1
ORDER BY SEGMENT ASC;

• CASE문으로 구매 구간을 3단계로 분류
• 신규/활성/충성 고객층 세분화 기반

 

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

---

 

💡 생각 정리

이번 문제는 데이터 요약 로직을 설계하는 기본기를 점검하기에 적합했다.
특히, 단순 조회보다 “비즈니스 인사이트를 위한 구간화(SEGMENT)” 접근이 중요했다.
예를 들어, 0회, 1회, 2회이상 구간으로 나누면 리텐션 및 구매 촉진 전략을 세우는 데 즉각적인 인사이트를 얻을 수 있다.

또한 HAVING MAX-MIN <> 0 같은 조건은 단순 집계를 정제하는 고급 필터링 방식으로, 데이터 분석 결과의 “품질 관리” 개념을 익힐 수 있었다.

---

🚀 적용점

 

• 신규/재구매 회원 구간을 자동 집계하는 리텐션 모니터링 SQL로 확장
• CASE WHEN 구조를 표준화해 BI 대시보드용 고객 세그먼트 생성
• HAVING 활용으로 이상치 제거 및 상품 단가 편차 감시 로직 구축

 

---

 

SQL 실전 대비 — 기출 예상 코딩 테스트 4

 

1. 모든 주요 페이지(login·home·product)에 방문한 사용자 수

WITH BASE AS (
  SELECT USER_ID, GROUP_CONCAT(DISTINCT PAGE ORDER BY PAGE ASC) AS PAGE_GROUP_CONCAT
  FROM VISIT
  GROUP BY USER_ID
)
SELECT COUNT(DISTINCT USER_ID) AS USER_CNT
FROM BASE
WHERE PAGE_GROUP_CONCAT = 'home,login,product';

• 3개 페이지 모두 방문한 USER_ID 추출
• GROUP_CONCAT으로 행동 패턴을 문자열로 묶어 필터링

---

2. 디바이스별 세션 수 요약

SELECT DEVICE_TYPE,
       COUNT(DISTINCT CASE WHEN PAGE='login' THEN SESSION_ID END) AS LOG_SESSION,
       COUNT(DISTINCT CASE WHEN PAGE='home' THEN SESSION_ID END) AS HOME_SESSION,
       COUNT(DISTINCT CASE WHEN PAGE='product' THEN SESSION_ID END) AS PRODUCT_SESSION
FROM VISIT
GROUP BY DEVICE_TYPE
ORDER BY DEVICE_TYPE ASC;

• 디바이스별 페이지 세션 분포 비교
• 멀티채널 유입 분석(MA) 기반의 데이터 구조

---

3. 2일 이상 로그인한 사용자 수

WITH BASE AS (
  SELECT USER_ID, COUNT(DISTINCT DATE(LOG_DATE)) AS LOG_DATE_DAY
  FROM VISIT
  WHERE PAGE='login'
  GROUP BY USER_ID
  HAVING COUNT(DISTINCT DATE(LOG_DATE)) > 1
)
SELECT COUNT(DISTINCT USER_ID) AS USER_CNT FROM BASE;

• “지속 방문자(리텐션 유저)” 계산 로직
• 단순 로그인 수가 아닌 날짜 단위 로그인일수 기준

---

4. 로그인만 하고 다른 페이지를 방문하지 않은 사용자

WITH LOGIN AS (
  SELECT * FROM VISIT WHERE PAGE='login'
),
OTHER AS (
  SELECT * FROM VISIT WHERE PAGE<>'login'
)
SELECT COUNT(DISTINCT LOGIN.USER_ID) AS USER_CNT,
       COUNT(DISTINCT LOGIN.SESSION_ID) AS SESSION_CNT
FROM LOGIN
LEFT JOIN OTHER ON LOGIN.USER_ID = OTHER.USER_ID
WHERE OTHER.USER_ID IS NULL;

• 이탈 가능성이 높은 “단일 접속 사용자” 식별
• 로그인 후 이탈(탈락) 패턴 탐지용

 

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

---

 

💡 생각 정리

이 문제는 전형적인 웹/앱 로그 분석 SQL로, 사용자의 행동 흐름을 데이터로 정리하는 사고력을 요구했다.

GROUP_CONCAT을 활용한 “행동 조합 패턴화”는 단순 방문 기록을 **세션 단위의 유저 여정(User Journey)**으로 바꾸는 강력한 도구였다.

또한 CASE WHEN으로 페이지별 세션 카운트를 나누는 방법은 실제 GA4, Amplitude, Snowflake 환경에서도 널리 쓰이는 방식이기에 실무 응용도가 높았다.

---

🚀 적용점

• 마케팅 퍼널(Funnel) 분석에 그대로 적용 가능
  • login → home → product 순서별 이탈률 계산
• 리텐션 측정
  • “2일 이상 로그인한 사용자” 지표를 DAU/MAU 계산의 기반으로 사용
• 유저 세그먼트 구축
  • 로그인만 한 유저 vs 다중 페이지 방문 유저로 구분하여 리타겟팅

👉 이번 세트는 SQL을 통해 “로그 데이터로 사용자의 행동을 해석하는 방법”을 배우는 단계로,
실제 데이터 분석가·마케팅 직무 코딩테스트와 가장 유사한 구성이다.