실전 문제 풀이 (71) - 신규 유저 비중 변화 / 월별 이탈률 / 사용자별 최대 연속 구매 월 수

문제 1 — 월별 신규 유저 비중 및 전월 대비 변화

DROP TABLE IF EXISTS orders_m2;
CREATE TABLE orders_m2 (
  order_id INT PRIMARY KEY,
  user_id INT,
  order_date DATE,
  amount DECIMAL(10,2)
);

INSERT INTO orders_m2 VALUES
(1, 1, '2025-06-02', 120),
(2, 2, '2025-06-10', 200),
(3, 1, '2025-06-18',  80),
(4, 3, '2025-07-01', 150),
(5, 2, '2025-07-05', 100),
(6, 4, '2025-07-15',  90),
(7, 1, '2025-07-20',  60),
(8, 5, '2025-08-03', 300),
(9, 3, '2025-08-10', 100),
(10,6, '2025-08-18',  70);

목표

월별로

• 전체 활성 사용자 수
• 해당 월에 처음으로 주문한 신규 사용자 수
• 신규 사용자 비중
• 신규 사용자 비중의 전월 대비 증감률(%)

을 계산하라. 첫 달의 증감률은 NULL로 표시한다.

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

• month : YYYY-MM 형식의 월
• active_users : 해당 월에 주문한 서로 다른 사용자 수
• new_users : 해당 월에 첫 주문을 한 사용자 수
• new_user_ratio : new_users / active_users (소수 둘째 자리)
• new_user_ratio_mom_pct : 전월 대비 신규 사용자 비중 증감률(%) (소수 둘째 자리)

WITH month_table AS (
  SELECT DISTINCT
    user_id,
    DATE_FORMAT(order_date, '%Y-%m') AS month
  FROM orders_m2
  ),
  
active_table AS (
  SELECT
    month,
    COUNT(*) AS active_users
  FROM month_table
  GROUP BY month
  ),

new_table AS (
  SELECT
    first_order_month,
    COUNT(*) AS new_users
  FROM (
    SELECT
      user_id,
      MIN(month) AS first_order_month
    FROM month_table
    GROUP BY user_id
    ) AS first_order_table
  GROUP BY first_order_month
  ),

join_table AS (
  SELECT
    a.month,
    a.active_users,
    n.new_users,
    ROUND(n.new_users / a.active_users, 2) AS new_user_ratio
  FROM active_table a
  JOIN new_table n
    ON a.month = n.first_order_month
  )
  
SELECT
  month,
  active_users,
  new_users,
  new_user_ratio,
  ROUND((new_user_ratio - LAG(new_user_ratio) OVER (ORDER BY month)) / LAG(new_user_ratio) OVER (ORDER BY month) * 100, 2) AS new_user_ratio_mom_pct
FROM join_table

 

 

 

문제 2 — 월별 이탈 유저 수 및 이탈률

DROP TABLE IF EXISTS orders_m5;
CREATE TABLE orders_m5 (
  order_id INT PRIMARY KEY,
  user_id INT,
  order_date DATE
);

INSERT INTO orders_m5 VALUES
(1, 1, '2025-06-01'),
(2, 2, '2025-06-05'),
(3, 3, '2025-06-20'),
(4, 1, '2025-07-03'),
(5, 2, '2025-07-10'),
(6, 4, '2025-07-15'),
(7, 1, '2025-08-01'),
(8, 5, '2025-08-05');

목표

월별로

• 전월에 활동했지만 해당 월에는 주문하지 않은 사용자 수(이탈 유저)
• 전월 활성 사용자 대비 이탈률

을 계산하라.
첫 달은 전월 정보가 없으므로 이탈 유저 수와 이탈률은 NULL로 표시한다.

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

• month : YYYY-MM
• prev_active_users : 전월에 주문한 서로 다른 사용자 수
• churn_users : 전월에는 주문했지만 해당 월에는 주문하지 않은 사용자 수
• churn_rate_pct : churn_users / prev_active_users × 100 (소수 둘째 자리)

WITH month_table AS (
  SELECT DISTINCT
    user_id,
    DATE_FORMAT(order_date, '%Y-%m-01') AS month
  FROM orders_m5
  ),
  
month_list_table AS (
  SELECT DISTINCT
    DATE_FORMAT(order_date, '%Y-%m-01') AS month
  FROM orders_m5
  )

SELECT
  DATE_FORMAT(mlt.month, '%Y-%m') AS month,
  COUNT(DISTINCT mt.user_id) AS prev_active_users,
  SUM(IF(mt2.user_id IS NULL, 1, 0)) AS churn_users,
  ROUND(SUM(IF(mt2.user_id IS NULL, 1, 0)) / COUNT(DISTINCT mt.user_id) * 100, 2) AS churn_rate_pct
FROM month_list_table mlt
LEFT JOIN month_table mt
  ON mt.month = DATE_SUB(mlt.month, INTERVAL 1 MONTH)
LEFT JOIN month_table mt2
  ON mt.user_id = mt2.user_id
  AND mlt.month = mt2.month
GROUP BY DATE_FORMAT(mlt.month, '%Y-%m')

 

 

 

문제 3 — 사용자별 최대 연속 구매 월 수

DROP TABLE IF EXISTS orders_m6;
CREATE TABLE orders_m6 (
  order_id INT PRIMARY KEY,
  user_id INT,
  order_date DATE
);

INSERT INTO orders_m6 VALUES
(1, 1, '2025-06-01'),
(2, 1, '2025-07-01'),
(3, 1, '2025-08-01'),
(4, 1, '2025-10-01'),
(5, 2, '2025-06-10'),
(6, 2, '2025-08-10'),
(7, 3, '2025-07-05'),
(8, 3, '2025-08-05'),
(9, 3, '2025-09-05'),
(10,3, '2025-10-05');

목표

각 사용자별로
연속해서 주문이 발생한 최대 개월 수를 계산하라.

• 같은 달에 여러 번 주문해도 1개월로 계산한다.
• 월이 끊기면 연속도 종료된다.

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

• user_id : 사용자 ID
• max_consecutive_months : 해당 사용자의 최대 연속 구매 개월 수

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

flag_table AS (
  SELECT
    user_id,
    month,
    DATE_SUB(month, INTERVAL ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY month
      ) - 1 MONTH) AS flag
  FROM month_table
  ),

consecutive_table AS (
  SELECT
    user_id,
    flag,
    COUNT(*) AS consecutive_months
  FROM flag_table
  GROUP BY user_id, flag
  )
  
SELECT
  user_id,
  MAX(consecutive_months) AS max_consecutive_months
FROM consecutive_table
GROUP BY user_id

 

 

---

 

1번 문제 맨 처음에 monthlist 테이블과 month 테이블을 결합할 때 그냥 JOIN을 사용했는데 이렇게 하면 month테이블에 아무것도 없다면 해당 달이 아예 사라지기 때문에 LEFT JOIN을 사용해야 한다고 한다.