[인프런 빅쿼리 빠짝스터디 3주차] 3주차 과제

1번

WITH 
-- 1단계: 필수 데이터만 추출하기
base_events AS (
    SELECT 
        user_pseudo_id,
        DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_date
    FROM advanced.app_logs
),

-- 2단계: 사용자별 첫 방문 주차와 각 활동 주차 구하기
user_weeks AS (
    SELECT DISTINCT  -- 중복 제거
        user_pseudo_id,
        -- 사용자별 첫 방문 주차 (월요일 기준)
        DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,
        -- 실제 방문한 주차 (월요일 기준)
        DATE_TRUNC(event_date, WEEK(MONDAY)) AS visit_week
    FROM base_events
),

-- 3단계: 첫 방문 이후 몇 주차인지 계산하기
week_numbers AS (
    SELECT 
        user_pseudo_id,
        -- 첫 방문 이후 경과된 주차 계산
        DATE_DIFF(visit_week, first_week, WEEK) AS week_number
    FROM user_weeks
    -- 최대 12주까지만 분석
    WHERE DATE_DIFF(visit_week, first_week, WEEK) <= 12
),

-- 4단계: 주차별 총 사용자 수 계산하기
weekly_users AS (
    SELECT 
        week_number,
        COUNT(DISTINCT user_pseudo_id) AS user_count
    FROM week_numbers
    GROUP BY week_number
)

-- 5단계: 최종 리텐션 계산하기
SELECT 
    week_number,
    user_count as active_users,
    FIRST_VALUE(user_count) OVER(ORDER BY week_number) as first_week_users,
    ROUND(100.0 * user_count / FIRST_VALUE(user_count) OVER(ORDER BY week_number), 2) as retention_rate
FROM weekly_users
ORDER BY week_number;

2번

WITH 
-- 1단계: 사용자별 주차 데이터 준비
user_weeks AS (
    SELECT DISTINCT
        user_pseudo_id,
        -- 첫 방문 주차
        DATE_TRUNC(MIN(DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul")) 
            OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,
        -- 활동 주차
        DATE_TRUNC(DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul"), 
            WEEK(MONDAY)) AS event_week
    FROM advanced.app_logs
),

-- 2단계: 사용자 상태 확인
user_status AS (
    SELECT 
        user_pseudo_id,
        event_week,
        first_week,
        -- 이전 방문 주차
        LAG(event_week) OVER(PARTITION BY user_pseudo_id ORDER BY event_week) AS prev_week
    FROM user_weeks
),

-- 3단계: 상태 분류
weekly_status AS (
    SELECT 
        event_week,
        user_pseudo_id,
        CASE
            WHEN event_week = first_week THEN 'New'
            WHEN DATE_DIFF(event_week, prev_week, WEEK) = 1 THEN 'Current'
            WHEN DATE_DIFF(event_week, prev_week, WEEK) > 1 THEN 'Resurrected'
        END as user_type
    FROM user_status
),

-- 4단계: 각 주차별 전체 유저 수
total_users AS (
    SELECT 
        fwd.event_week,
        COUNT(DISTINCT user_pseudo_id) AS total_user_count
    FROM user_status
    CROSS JOIN (SELECT DISTINCT event_week FROM user_weeks) AS fwd
    WHERE first_week <= fwd.event_week
    GROUP BY event_week
),

-- 5단계: 주차별 활성 유저 수 계산
active_users AS (
    SELECT 
        event_week,
        COUNTIF(user_type = 'New') AS new_users,
        COUNTIF(user_type = 'Current') AS current_users,
        COUNTIF(user_type = 'Resurrected') AS resurrected_users,
        COUNT(DISTINCT user_pseudo_id) AS retain_users
    FROM weekly_status 
    GROUP BY event_week
)

-- 6단계: 최종 결과
SELECT 
    FORMAT_DATE('%Y-%m-%d', a.event_week) as week_start,
    new_users,
    current_users,
    resurrected_users,
    (t.total_user_count - a.retain_users) as dormant_users,
    ROUND(100.0 * current_users / NULLIF(LAG(new_users) 
        OVER(ORDER BY a.event_week), 0), 1) as retention_rate,
    retain_users as active_users,
    t.total_user_count as total_users
FROM active_users a
JOIN total_users t ON a.event_week = t.event_week
ORDER BY a.event_week;

3번

WITH 
-- 1단계: 기본 데이터 준비
user_weeks AS (
    SELECT DISTINCT
        user_pseudo_id,
        DATE_TRUNC(MIN(DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul")) 
            OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,
        DATE_TRUNC(DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul"), 
            WEEK(MONDAY)) AS event_week
    FROM advanced.app_logs
),

-- 2단계: 사용자별 방문 주차 수 계산
user_visit_frequency AS (
    SELECT 
        user_pseudo_id,
        COUNT(DISTINCT event_week) as total_visit_weeks,
        DATE_DIFF(MAX(event_week), MIN(event_week), WEEK) + 1 as weeks_since_first,
        MIN(event_week) as first_visit_week
    FROM user_weeks
    GROUP BY user_pseudo_id
),

-- 3단계: 사용자별 리텐션 점수 계산
user_retention_score AS (
    SELECT 
        user_pseudo_id,
        total_visit_weeks,
        weeks_since_first,
        ROUND(100.0 * total_visit_weeks / weeks_since_first, 2) as visit_rate,
        FORMAT_DATE('%Y-%m', first_visit_week) as cohort_month
    FROM user_visit_frequency
    WHERE weeks_since_first >= 4  -- 최소 4주 이상 경과된 사용자만
),

-- 4단계: 사용자 행동 데이터 분석
user_behavior AS (
    SELECT 
        r.user_pseudo_id,
        r.visit_rate,
        r.cohort_month,
        COUNT(DISTINCT DATE(TIMESTAMP_MICROS(l.event_timestamp), "Asia/Seoul")) as active_days,
        COUNT(DISTINCT l.event_timestamp) as total_events,
        COUNT(DISTINCT l.event_name) as unique_event_types
    FROM user_retention_score r
    JOIN advanced.app_logs l ON r.user_pseudo_id = l.user_pseudo_id
    GROUP BY r.user_pseudo_id, r.visit_rate, r.cohort_month
),

-- 5단계: 리텐션 세그먼트별 행동 패턴 분석
retention_segments AS (
    SELECT 
        cohort_month,
        CASE 
            WHEN visit_rate >= 75 THEN 'Very High (75%+)'
            WHEN visit_rate >= 50 THEN 'High (50-74%)'
            WHEN visit_rate >= 25 THEN 'Medium (25-49%)'
            ELSE 'Low (<25%)'
        END as retention_segment,
        COUNT(DISTINCT user_pseudo_id) as user_count,
        ROUND(AVG(visit_rate), 2) as avg_retention_rate,
        ROUND(AVG(active_days), 1) as avg_active_days,
        ROUND(AVG(total_events), 1) as avg_total_events,
        ROUND(AVG(unique_event_types), 1) as avg_unique_events
    FROM user_behavior
    GROUP BY 
        cohort_month,
        CASE 
            WHEN visit_rate >= 75 THEN 'Very High (75%+)'
            WHEN visit_rate >= 50 THEN 'High (50-74%)'
            WHEN visit_rate >= 25 THEN 'Medium (25-49%)'
            ELSE 'Low (<25%)'
        END
)

-- 최종 결과
SELECT 
    cohort_month,
    retention_segment,
    user_count,
    avg_retention_rate,
    avg_active_days,
    avg_total_events,
    avg_unique_events,
    ROUND(100.0 * user_count / SUM(user_count) OVER (PARTITION BY cohort_month), 2) as segment_percentage
FROM retention_segments
ORDER BY 
    cohort_month,
    CASE retention_segment
        WHEN 'Very High (75%+)' THEN 1
        WHEN 'High (50-74%)' THEN 2
        WHEN 'Medium (25-49%)' THEN 3
        WHEN 'Low (<25%)' THEN 4
    END;

4번

WITH 
-- 1단계: 결제 이벤트 기본 데이터
payment_base AS (
    SELECT DISTINCT
        user_pseudo_id,
        DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_date
    FROM advanced.app_logs
    WHERE event_name = "click_payment"
),

-- 2단계: 사용자별 첫 결제일과 주차 계산
user_weeks AS (
    SELECT 
        user_pseudo_id,
        -- 첫 결제 주차
        DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,
        -- 실제 결제 주차
        DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week
    FROM payment_base
),

-- 3단계: 주차별 상태 계산
week_status AS (
    SELECT 
        user_pseudo_id,
        event_week,
        first_week,
        -- 첫 주차와의 차이
        DATE_DIFF(event_week, first_week, WEEK) AS week_number
    FROM user_weeks
    WHERE DATE_DIFF(event_week, first_week, WEEK) <= 12  -- 최대 12주까지만 분석
),

-- 4단계: 주차별 활성 사용자 수 계산
weekly_users AS (
    SELECT 
        week_number,
        COUNT(DISTINCT user_pseudo_id) AS active_users
    FROM week_status
    GROUP BY week_number
),

-- 5단계: 첫 주 사용자 수 (코호트 크기) 구하기
first_week_users AS (
    SELECT 
        COUNT(DISTINCT user_pseudo_id) AS cohort_size
    FROM week_status 
    WHERE week_number = 0
)

-- 최종 결과
SELECT 
    week_number as week,
    active_users,
    cohort_size,
    ROUND(100.0 * active_users / cohort_size, 2) as retention_rate
FROM weekly_users
CROSS JOIN first_week_users
ORDER BY week_number;

서비스 성장세

총 사용자: 52,823명까지 증가
8월부터 12월까지 꾸준한 성장
10월에 가장 높은 신규 유저 유입 (4,048명)

리텐션 개선

전체 리텐션: 8월 2.6% → 1월 48%로 큰 폭 개선
12월부터 40% 이상의 안정적인 리텐션 유지
재방문 사용자 비중이 지속적으로 증가

개선 필요점

결제 리텐션이 매우 낮음 (1% 수준)
신규 사용자 유입이 감소 추세
휴면 사용자가 지속적으로 증가

인프런 커뮤니티 질문&답변

서비스 성장세

리텐션 개선

개선 필요점

질문 요약 및 해결 방법:

참고자료: