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

 12. 기출 예상 코딩 테스트

 

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

1. 회원별 주문 이력 및 매출 요약

문제:
가입한 회원별로 최근 구매일자(MAX_ORDER_DATE), 주문수(ORDER_NO_DISTINCT_COUNT),
매출액(SALES_AMT_SUM), 주문 이력 여부(ORDER_YN)를 조회하시오.

WITH BASE AS (
SELECT  A.MEM_NO,
        MAX(ORDER_DATE) AS MAX_ORDER_DATE,
        COUNT(DISTINCT B.ORDER_NO) AS ORDER_NO_DISTINCT_COUNT,
        SUM(QTY * PRICE) AS SALES_AMT_SUM
  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
 GROUP BY A.MEM_NO
)
SELECT *,
       CASE WHEN MAX_ORDER_DATE IS NOT NULL THEN 'Y' ELSE 'N' END AS ORDER_YN
  FROM BASE
 ORDER BY MEM_NO DESC;

 

핵심 포인트

• LEFT JOIN으로 주문이 없는 회원도 포함
• 매출 계산식: SUM(QTY * PRICE)
• ORDER_YN은 주문 유무를 명시적으로 표시해 데이터 결측 확인 가능

---

2. 주문수 2회 이상 회원의 서비스별 주문 비중

문제:
주문이 2회 이상인 회원들의 주문수를 전체 회원 대비 비중으로 계산하고,
서비스별 비중(PORTION)을 높은 순으로 조회하시오.

 

WITH BASE AS (
SELECT  A.MEM_NO,
        COUNT(DISTINCT B.ORDER_NO) AS ORDER_NO_DISTINCT_COUNT
  FROM  CUSTOMER AS A
  INNER JOIN SALE AS B ON A.MEM_NO = B.MEM_NO
 GROUP BY A.MEM_NO
HAVING COUNT(DISTINCT B.ORDER_NO) > 1
),
ORDER_BY_SERVICE_NAME AS (
SELECT  SERVICE_NAME,
        COUNT(DISTINCT B.ORDER_NO) AS ORDER_NO_DISTINCT_COUNT,
        COUNT(DISTINCT CASE WHEN BASE.MEM_NO IS NOT NULL THEN B.ORDER_NO END) AS ORDER_NO_DISTINCT_COUNT_2_OVER
  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 BASE ON A.MEM_NO = BASE.MEM_NO
 GROUP BY SERVICE_NAME
)
SELECT SERVICE_NAME,
       ROUND(ORDER_NO_DISTINCT_COUNT_2_OVER/ORDER_NO_DISTINCT_COUNT,2) AS PORTION
  FROM ORDER_BY_SERVICE_NAME
 ORDER BY 2 DESC;

 

핵심 포인트

• HAVING COUNT > 1 조건으로 충성 고객만 추출
• 전체 주문 대비 다회 구매 고객의 주문 비중을 산출
• 브랜드 충성도 높은 서비스 파악에 활용 가능

---

3. 주문별 다중 서비스 구매 패턴 분석

문제:
하나의 주문(ORDER_NO)에서 2가지 이상의 SERVICE_NAME을 구매한 패턴을 그룹화하시오.
그룹화 컬럼명은 SERVICE_NAME_GROUP, 구분자는 쉼표(,)이다.

 

WITH BASE AS (
SELECT  A.ORDER_NO,
        GROUP_CONCAT(DISTINCT SERVICE_NAME ORDER BY SERVICE_NAME ASC) AS SERVICE_NAME_GROUP
  FROM  SALE AS A
  LEFT JOIN SALE_DETAIL AS B ON A.ORDER_NO = B.ORDER_NO
  LEFT JOIN SERVICE AS C ON B.SERVICE_CODE = C.SERVICE_CODE
 GROUP BY A.ORDER_NO
)
SELECT SERVICE_NAME_GROUP,
       COUNT(DISTINCT ORDER_NO) AS ORDER_NO_DISTINCT_COUNT
  FROM BASE
 WHERE SERVICE_NAME_GROUP LIKE '%,%'
 GROUP BY SERVICE_NAME_GROUP
 ORDER BY ORDER_NO_DISTINCT_COUNT DESC, SERVICE_NAME_GROUP DESC;

 

핵심 포인트

• GROUP_CONCAT(DISTINCT …)으로 다중 구매 조합 그룹화
• LIKE '%,%'로 다중 서비스 구매 주문만 필터링
• ORDER_NO_DISTINCT_COUNT로 인기 조합 TOP N 파악 가능

💡 예시: "room,food" → 숙박+식음료 패키지 구매 고객군

---

4. 가입연도별 평균 구매주기 분석

문제:
회원의 가입연도(JOIN_DATE_YY)별 평균 구매주기(AVG_CIRCLE)를 조회하시오.
구매주기 = (최근구매일자 - 최초구매일자) / (구매횟수 - 1)

 

WITH BASE AS (
SELECT  A.MEM_NO,
        DATE_FORMAT(JOIN_DATE, '%Y') AS JOIN_DATE_YY,
        MIN(ORDER_DATE) AS MIN_ORDER_DATE,
        MAX(ORDER_DATE) AS MAX_ORDER_DATE,
        COUNT(DISTINCT ORDER_NO) AS COUNT_DISTINCT_ORDER_NO
  FROM  CUSTOMER AS A
  INNER JOIN SALE AS B ON A.MEM_NO = B.MEM_NO
 GROUP BY A.MEM_NO, DATE_FORMAT(JOIN_DATE, '%Y')
),
BASE_CIRCLE AS (
SELECT *,
       DATEDIFF(MAX_ORDER_DATE, MIN_ORDER_DATE) / (COUNT_DISTINCT_ORDER_NO - 1) AS CIRCLE
  FROM BASE
 WHERE COUNT_DISTINCT_ORDER_NO > 1
)
SELECT JOIN_DATE_YY,
       ROUND(AVG(CIRCLE),2) AS AVG_CIRCLE
  FROM BASE_CIRCLE
 GROUP BY JOIN_DATE_YY
 ORDER BY 1 ASC;

 

핵심 포인트

• 구매주기 공식 활용: (MAX−MIN)/(COUNT−1)
• 연도별 평균 재구매 주기 파악 가능
• 신규 고객 vs 장기 고객 간 재방문 주기 비교에 유용

 

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

---

 

💡 생각 정리

이번 코딩 테스트 문제들은 단순한 SQL 문법 이해를 넘어, “데이터에서 인사이트를 도출하는 사고력”을 요구했다.

특히 인상 깊었던 부분은 다음과 같다:

• GROUP_CONCAT을 통한 다중 서비스 조합 분석은 실제 상품 패키징 기획에서 자주 쓰이는 패턴이다.
• 구매주기 공식 (MAX - MIN) / (COUNT - 1)은
  Pandas나 Python 없이도 SQL 내에서 고객 리텐션을 정량화할 수 있게 한다.
• CTE를 단계적으로 나눠 작성함으로써 쿼리의 가독성과 유지보수성을 높이는 점이 실무적으로 매우 유용했다.

---

🚀 적용점

• CRM 마케팅 적용
  • 다회 구매 고객 비중이 높은 서비스에 리워드 캠페인 집중
  • 구매주기가 긴 고객군에 리마인드 메시지 자동 발송
• BI 대시보드 설계
  • JOIN_DATE_YY별 평균 구매주기를 KPI로 설정
  • SERVICE_NAME_GROUP별 인기 조합을 태그형 시각화
• 코딩 테스트 준비
  • CTE + GROUP_CONCAT + DATE_FORMAT 조합 문제를 반복 연습
  • 단순 집계 쿼리보다 “의도 파악 + 분석 로직” 중심으로 사고 전환

👉 이번 세트는 단순한 SQL 학습을 넘어,
데이터 분석가의 사고력·논리적 설계력·비즈니스 해석력을 동시에 평가하는 실전형 문제였다.