C사 SQL 쿼리테스트 대비: 재구매율 분석

1. [재구매율 분석] 특정 기간 내 재구매 고객 비율 산출

[문제 정의]

쿠팡에서는 고객의 충성도를 측정하기 위해 '재구매율'을 중요하게 관리합니다. 2025년 1월에 첫 구매를 한 고객들 중, 30일 이내에 두 번째 구매를 발생시킨 고객의 비율을 구하십시오.

• 테이블: Orders (컬럼: order_id, customer_id, order_date)
• 결과: repurchase_rate (소수점 둘째 자리까지)

■ 풀이 방법

이 문제는 **'최초 구매일'**이라는 기준점과 **'그 이후의 행동'**을 연결해야 합니다.

1. 유저별 최초 구매일을 찾고 (MIN),
2. 해당 유저의 전체 구매 기록과 조인하여 1일 이상 30일 이하의 간격이 존재하는지 확인합니다.
3. 단순히 "구매 횟수가 2회 이상인 사람"은 재구매가 아니라 그냥 '다회 구매자'입니다. 쿠팡은 **'특정 기간(30일) 내의 재구매'**라는 시간적 제약(Recency)을 설계할 수 있는지를 봅니다. 

쿠팡형 정석 쿼리 (Self Join + CTE)

WITH FirstOrder AS (
    -- 2025년 1월에 처음 구매한 유저와 그 날짜를 추출
    SELECT customer_id, MIN(order_date) as first_date
    FROM Orders
    GROUP BY customer_id
    HAVING DATE_FORMAT(MIN(order_date), '%Y-%m') = '2025-01'
),
RepurchaseUsers AS (
    -- 첫 구매 이후 30일 이내에 다시 구매한 유저 식별
    SELECT DISTINCT f.customer_id
    FROM FirstOrder f
    JOIN Orders o ON f.customer_id = o.customer_id
    WHERE o.order_date > f.first_date 
      AND o.order_date <= DATE_ADD(f.first_date, INTERVAL 30 DAY)
)
SELECT 
    ROUND(
        (SELECT COUNT(*) FROM RepurchaseUsers) / 
        (SELECT COUNT(*) FROM FirstOrder) * 100, 2
    ) AS repurchase_rate;