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

 12. 기출 예상 코딩 테스트

 

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

 

1. 신규 가입자 구매 전환율 계산

문제:
2024년 1월에 신규로 회원가입을 하고, 가입일로부터 3일 이내에 구매를 완료한 회원 수를 추출하시오.
(컬럼명: MEM_CNT)

SELECT COUNT(DISTINCT A.MEM_NO) AS MEM_CNT
FROM CUSTOMER AS A
INNER JOIN SALE AS B
  ON A.MEM_NO = B.MEM_NO
WHERE DATE_FORMAT(JOIN_DATE, '%Y-%m') = '2024-01'
  AND DATEDIFF(ORDER_DATE, JOIN_DATE) <= 3;

 

핵심 포인트

• 신규 가입자 전환율 = “가입 후 구매까지 걸린 기간 ≤ 3일”
• DATEDIFF()로 기간 계산, JOIN_DATE와 ORDER_DATE 비교
• 온보딩 이후 구매 전환율 측정 로직에 자주 활용됨

---

2. 누적 구매수량이 5 도달 시점 계산

문제:
2024년 구매한 회원 중, 누적 구매수량이 5가 되는 최초 구매일자를 기준으로
2024-01-01로부터 소요된 평균 일수를 구하시오 (AVG_DAY).

WITH BASE AS (
  SELECT A.MEM_NO, ORDER_DATE,
         SUM(QTY) OVER (PARTITION BY A.MEM_NO ORDER BY ORDER_DATE ASC) AS SUM_AGG_QTY
  FROM CUSTOMER AS A
  INNER JOIN SALE AS B ON A.MEM_NO = B.MEM_NO
  LEFT JOIN SALE_DETAIL AS C ON B.ORDER_NO = C.ORDER_NO
  WHERE DATE_FORMAT(ORDER_DATE, '%Y') = '2024'
),
FIRST_QTY_5_OVER AS (
  SELECT MEM_NO, MIN(ORDER_DATE) AS MIN_ORDER_DATE
  FROM BASE
  WHERE SUM_AGG_QTY >= 5
  GROUP BY MEM_NO
)
SELECT ROUND(AVG(DATEDIFF(MIN_ORDER_DATE, '2024-01-01')), 2) AS AVG_DAY
FROM FIRST_QTY_5_OVER;

 

핵심 포인트

• WINDOW FUNCTION + CTE를 활용한 누적 수량 계산
• 실제 기업의 **“구매 행동 분석”**이나 리텐션 분석에 사용되는 로직
• 누적 조건 충족 시점을 MIN()으로 추출

---

3. 특정 기간 다중 카테고리 구매자 추적

문제:
2025년에 room과 Food and drink 타입을 모두 구매한 회원이
2024년 7월에 어떤 room 타입 SERVICE_NAME을 주문했는지를 조회하시오.

 

WITH BASE AS (
  SELECT A.MEM_NO, ORDER_DATE, TYPE, SERVICE_NAME
  FROM CUSTOMER AS A
  INNER 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
),
TYPE_2_OVER_2025 AS (
  SELECT MEM_NO
  FROM BASE
  WHERE DATE_FORMAT(ORDER_DATE, '%Y') = '2025'
  GROUP BY MEM_NO
  HAVING COUNT(DISTINCT TYPE) > 1
)
SELECT SERVICE_NAME
FROM BASE AS A
INNER JOIN TYPE_2_OVER_2025 AS B ON A.MEM_NO = B.MEM_NO
WHERE DATE_FORMAT(ORDER_DATE, '%Y-%m') = '2024-07'
  AND TYPE = 'room';

 

핵심 포인트

• 연도별 행동 비교, “복합 구매자 행동” 탐색
• HAVING COUNT(DISTINCT TYPE) > 1 조건으로 다중 카테고리 고객 추출
• CRM·리텐션 마케팅 분석에서 “교차 구매(Cross-sell)” 타깃 식별에 활용 가능

---

4. 특정 연도 간 중복 구매자 분석

문제:
2023년에 구매한 고객 중 2025년에도 구매한 고객을 성별별로 집계하시오 (MEM_CNT).

 

SELECT GENDER, COUNT(DISTINCT A.MEM_NO) AS MEM_CNT
FROM CUSTOMER AS A
INNER JOIN SALE AS B ON A.MEM_NO = B.MEM_NO
WHERE YEAR(ORDER_DATE) IN (2023, 2025)
GROUP BY 1
HAVING COUNT(DISTINCT YEAR(ORDER_DATE)) = 2
ORDER BY GENDER ASC;

 

핵심 포인트

• HAVING 절에서 다중 연도 교집합 계산
• 성별별 구매 지속률 분석
• 고객 충성도/이탈 방지 지표 설계에 응용 가능

---

5. 호텔별 구매 효율 분석

문제:
HOTEL 명에 seoul이 포함된 주문에 대해
날짜별 구매수량 합계를 구매자 수로 나눈 값을 구하시오 (QTY_BY_MEM).

 

SELECT DATE_FORMAT(ORDER_DATE, '%Y-%m-%d') AS ORDER_DATE_YMD,
       ROUND(SUM(QTY) / COUNT(DISTINCT A.MEM_NO), 2) AS QTY_BY_MEM
FROM CUSTOMER AS A
INNER 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
LEFT JOIN HOTEL AS E ON D.HOTEL_CODE = E.HOTEL_CODE
WHERE HOTEL LIKE '%seoul%'
GROUP BY DATE_FORMAT(ORDER_DATE, '%Y-%m-%d')
ORDER BY ORDER_DATE_YMD ASC;

 

핵심 포인트

• 구매 효율 = 총 구매수량 / 구매자 수
• 지역 기반 호텔 성과 비교용 분석 로직
• 마케팅 성수기별 구매 패턴 분석에 활용 가능

 

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

---

 

💡 생각 정리

이번 실습은 단순 SQL 문법이 아니라, 실제 코딩 테스트·데이터 직무 인터뷰에서 요구되는 사고방식을 훈련할 수 있었다.

특히 인상 깊었던 점은:

• WINDOW FUNCTION, HAVING, CTE를 복합적으로 사용하는 문제 구조
• 단순 집계를 넘어 비즈니스 로직이 담긴 쿼리를 직접 구현했다는 점
• 시간 조건(DATEDIFF, DATE_FORMAT)을 통해 이벤트 기반 고객 행동 분석을 SQL로 수행한 경험

SQL이 단순 데이터 추출을 넘어서, 데이터 기반 사고(Data-driven Thinking) 를 검증하는 도구임을 다시 느꼈다.

---

🚀 적용점

• 코딩 테스트 대비
  • CTE / WINDOW / CASE / DATE 함수 중심으로 반복 연습
  • 문제 풀이 시 반드시 “비즈니스 맥락”을 함께 고려하기
• 실무 적용
  • 신규가입 → 구매 전환율 → 재구매율까지 파이프라인 분석
  • 연도별 교차 고객, 지역별 구매 효율을 시각화
  • “누적 조건 기반 첫 발생일자” 로직을 리텐션 분석에 바로 활용

👉 이번 강의는 단순 SQL 시험 대비가 아니라, 실제 데이터 분석가·BI 개발자의 사고방식을 훈련하는 최적의 실습 세트였다.