실전 문제 풀이 (70) - 재구매율 전월 대비 변화 / 누적 주문금액 순위 / 재방문까지 걸린 평균 일수

문제 1 — 월별 재구매율과 전월 대비 변화

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

INSERT INTO orders_p1 (order_id, user_id, order_date) VALUES
(1, 1, '2025-06-02'),
(2, 2, '2025-06-05'),
(3, 1, '2025-06-20'),
(4, 3, '2025-06-25'),
(5, 1, '2025-07-01'),
(6, 2, '2025-07-03'),
(7, 2, '2025-07-20'),
(8, 4, '2025-07-22'),
(9, 5, '2025-08-01'),
(10,1, '2025-08-03'),
(11,2, '2025-08-10'),
(12,2, '2025-08-21');

목표

월별로

• 주문한 사용자 수
• 해당 월에 2회 이상 주문한 사용자 수
• 재구매율(%)
• 전월 대비 재구매율 변화(%p)

를 계산하라.
첫 달의 전월 대비 변화는 NULL로 표시한다.

출력 컬럼 설명

month	주문 월 (YYYY-MM)
total_users	해당 월에 주문한 서로 다른 사용자 수
repeat_users	해당 월에 2회 이상 주문한 사용자 수
repeat_rate_pct	repeat_users / total_users × 100 (소수 둘째 자리)
mom_repeat_rate_diff	전월 대비 재구매율 변화(%p), 첫 달은 NULL

WITH order_cnt_table AS (
  SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    user_id,
    COUNT(*) AS order_cnt
  FROM orders_p1
  GROUP BY DATE_FORMAT(order_date, '%Y-%m'), user_id
  ),

repeat_table AS (
  SELECT
    month,
    COUNT(*) AS total_users,
    SUM(IF(order_cnt >= 2, 1, 0)) AS repeat_users,
    SUM(IF(order_cnt >= 2, 1, 0)) / COUNT(*) AS repeat_rate_pct
  FROM order_cnt_table
  GROUP BY month
  )
  
SELECT
  *,
  ROUND(repeat_rate_pct - LAG(repeat_rate_pct) OVER (ORDER BY month), 2)
FROM repeat_table

 

 

 

문제 2 — 사용자별 누적 주문금액과 월별 순위

DROP TABLE IF EXISTS payments_p2;
CREATE TABLE payments_p2 (
  payment_id INT PRIMARY KEY,
  user_id INT,
  payment_date DATE,
  amount INT
);

INSERT INTO payments_p2 VALUES
(1, 1, '2025-06-01', 100),
(2, 2, '2025-06-03', 300),
(3, 1, '2025-06-20', 200),
(4, 3, '2025-06-25', 400),
(5, 1, '2025-07-05', 150),
(6, 2, '2025-07-10', 100),
(7, 3, '2025-07-20', 200),
(8, 4, '2025-07-22', 500),
(9, 2, '2025-08-01', 300),
(10,3,'2025-08-03', 100);

목표

각 월마다 사용자별로

• 해당 월 결제 금액
• 해당 사용자 기준 누적 결제 금액
• 해당 월 결제 금액 기준 순위

를 구하라.
동일 금액 사용자는 같은 순위를 갖는다.

출력 컬럼 설명

month	결제 월 (YYYY-MM)
user_id	사용자 ID
monthly_amount	해당 월 사용자 결제 금액
cumulative_amount	사용자별 누적 결제 금액
monthly_rank	해당 월 결제 금액 기준 순위

WITH monthly_amount_table AS (
  SELECT
    DATE_FORMAT(payment_date, '%Y-%m') AS month,
    user_id,
    SUM(amount) AS monthly_amount
  FROM payments_p2
  GROUP BY DATE_FORMAT(payment_date, '%Y-%m'), user_id
  )
  
SELECT
  *,
  SUM(monthly_amount) OVER (
    PARTITION BY user_id
    ORDER BY month
    ) AS cumulative_amount,
  RANK() OVER (
    PARTITION BY month
    ORDER BY monthly_amount DESC
    ) AS monthly_rank
FROM monthly_amount_table

 

 

 

문제 3 — 첫 구매 이후 재방문까지 걸린 평균 일수

DROP TABLE IF EXISTS visits_p3;
CREATE TABLE visits_p3 (
  visit_id INT PRIMARY KEY,
  user_id INT,
  visit_date DATE
);

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

목표

각 사용자에 대해

• 첫 방문일
• 첫 방문 이후 두 번째 방문까지 걸린 일수

를 계산하고,
전체 사용자 기준 평균 재방문 소요 일수를 구하라.
(두 번째 방문이 없는 사용자는 제외)

출력 컬럼 설명

user_id	사용자 ID
first_visit_date	첫 방문 날짜
days_to_second_visit	첫 방문 → 두 번째 방문까지의 일수
avg_days_to_second_visit	전체 사용자 평균 재방문 소요 일수

WITH window_table AS (
  SELECT
    user_id,
    visit_date,
    LEAD(visit_date) OVER (
      PARTITION BY user_id
      ORDER BY visit_date
      ) AS next_visit_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY visit_date
      ) AS rn
  FROM visits_p3
  )
  
SELECT
  user_id,
  visit_date AS first_visit_date,
  DATEDIFF(next_visit_date, visit_date) AS days_to_second_visit,
  ROUND(AVG(DATEDIFF(next_visit_date, visit_date)) OVER (), 2) AS avg_days_to_second_visit
FROM window_table
WHERE rn = 1 
  AND next_visit_date IS NOT NULL

 

 

---