실전 문제 풀이(64) - 최초 유입 사용자 수 / 영구 이탈 사용자 수 / 2개월 휴면 후 복귀한 사용자 수

문제 1 — 월별 최초 유입 사용자 수

DROP TABLE IF EXISTS user_activity_s1;
CREATE TABLE user_activity_s1 (
  user_id INT,
  activity_date DATE
);

INSERT INTO user_activity_s1 (user_id, activity_date) VALUES
(1, '2025-06-05'),
(2, '2025-06-10'),
(1, '2025-07-01'),
(3, '2025-07-03'),
(4, '2025-08-15');

목표

월별로 해당 월에 처음으로 활동한 사용자 수를 구하라.

출력(요구사항) — 컬럼 설명

• month
  • YYYY-MM 형식
• new_users
  • 해당 월이 첫 활동 월인 사용자 수

WITH group_table AS (
  SELECT
    user_id,
    MIN(activity_date) AS first_active_date
  FROM user_activity_s1
  GROUP BY user_id
  )
  
SELECT
  DATE_FORMAT(first_active_date, '%Y-%m') AS month,
  COUNT(*) AS new_users
FROM group_table
GROUP BY DATE_FORMAT(first_active_date, '%Y-%m')

 

 

 

문제 2 — 월별 영구 이탈 사용자 수

DROP TABLE IF EXISTS user_activity_s2;
CREATE TABLE user_activity_s2 (
  user_id INT,
  activity_date DATE
);

INSERT INTO user_activity_s2 (user_id, activity_date) VALUES
(1, '2025-06-05'),
(1, '2025-07-10'),
(2, '2025-06-15'),
(3, '2025-07-01'),
(4, '2025-08-03');

목표

각 월을 기준으로
해당 월 이후에 다시는 활동하지 않은 사용자 수를 구하라.

출력(요구사항) — 컬럼 설명

• month
  • YYYY-MM 형식
• churn_users
  • 기준 월 이후 활동 기록이 전혀 없는 사용자 수

WITH month_table AS (
  SELECT DISTINCT
    user_id,
    DATE_FORMAT(activity_date, '%Y-%m') AS month
  FROM user_activity_s2
  ),

group_table AS (
  SELECT
    user_id,
    MAX(month) AS last_month
  FROM month_table
  GROUP BY user_id
  )
  
SELECT
  last_month,
  COUNT(*) AS churn_users
FROM group_table
GROUP BY last_month
ORDER BY last_month

 

 

 

문제 3 — 월별 휴면 후 재활성화 사용자 수

DROP TABLE IF EXISTS user_activity_s3_v2;
CREATE TABLE user_activity_s3_v2 (
  user_id INT,
  activity_date DATE
);

INSERT INTO user_activity_s3_v2 (user_id, activity_date) VALUES
(1, '2025-06-01'),
(1, '2025-09-03'),
(2, '2025-07-10'),
(2, '2025-08-05'),
(3, '2025-06-15'),
(3, '2025-08-20'),
(4, '2025-08-01'),
(5, '2025-09-10');

목표

월별로 휴면 후 재활성화 사용자 수를 구하라.

휴면 후 재활성화 사용자 정의

• 과거 활동 이력이 존재하고
• 직전 2개월 동안 활동 기록이 없으며
• 해당 월에 활동한 사용자

출력(요구사항) — 컬럼 설명

• month
  • YYYY-MM 형식
• reactivated_users
  • 정의에 해당하는 사용자 수

WITH month_table AS (
  SELECT DISTINCT
    user_id,
    DATE_FORMAT(activity_date, '%Y-%m-01') AS month
  FROM user_activity_s3_v2
  ),

reactive_table AS (
  SELECT
    m1.user_id,
    m1.month
  FROM month_table m1
  LEFT JOIN month_table m2  
    ON m1.user_id = m2.user_id
    AND m2.month < m1.month
    AND m2.month > DATE_SUB(m1.month, INTERVAL 2 MONTH)
  WHERE m2.user_id IS NULL
  AND EXISTS (
    SELECT 1
    FROM month_table m3
    WHERE m1.user_id = m3.user_id
    AND m3.month < m1.month
    )
  )
  
SELECT
  DATE_FORMAT(month, '%Y-%m') AS month,
  COUNT(*) AS reactived_users
FROM reactive_table
GROUP BY DATE_FORMAT(month, '%Y-%m')

 

 

---

요즘 우아한청년들 서류를 기다리는 중이다. 채용공고에 유입, 이탈, 복귀 관련 지표를 다룬다고 써있길래 그것들과 관련된 문제를 위주로 풀고있는데 이제 꽤 감을 잡았다. 난이도를 조금 높여봐야겠다.