다음은 사용자가 특정 날짜에 문제를 푼 후, 다음날 다시 문제를 푸는 비율을 SQL로 계산하는 방법에 대한 설명이다. 이 지표는 일반적으로 '익일 유저 잔존률(Day-1 Retention Rate)'이라고 하며, 제품의 사용자 참여도를 평가하는 데 자주 활용된다.

테이블 구조 예시

아래와 같은 question_practice_detail 테이블이 있다고 가정하자.

목표

각 사용자가 한 번이라도 문제를 푼 날짜 기준으로, 그로부터 정확히 다음날에도 문제를 푼 경우를 카운트하여 전체 활성 사용자 대비 비율을 소수점 넷째 자리까지 반올림해 출력한다. 결과는 다음과 같아야 한다.

핵심 함수: DATE_ADD

MySQL에서 날짜 연산을 위해 DATE_ADD(date, INTERVAL n DAY) 함수를 사용할 수 있다. 예를 들어, DATE_ADD('2021-08-13', INTERVAL 1 DAY)는 '2021-08-14'를 반환한다. 이를 통해 특정 날짜의 다음날을 쉽게 표현할 수 있다.

쿼리 작성 전략

1. 사용자의 각 방문 일자를 고유하게 추출: 동일한 기기(device_id)가 동일한 날짜에 여러 문제를 풀어도 하나의 로그인 이벤트로 간주해야 하므로, DISTINCT device_id, date로 중복 제거.
2. 기준 집합과 다음날 집합 조인: 오늘 방문한 사용자 집합(A)과 내일 방문한 사용자 집합(B)을 LEFT JOIN하여, A의 사용자가 B에도 존재하는지 확인.
3. 비율 계산: 전체 첫 방문 수 대비 다음날 재방문 성공 건수의 비율을 산출.

최종 쿼리

SELECT 
    ROUND(COUNT(b.device_id) / COUNT(a.device_id), 4) AS avg_ret
FROM 
    (SELECT DISTINCT device_id, date FROM question_practice_detail) a
LEFT JOIN 
    (SELECT DISTINCT device_id, date FROM question_practice_detail) b
ON 
    a.device_id = b.device_id 
    AND b.date = DATE_ADD(a.date, INTERVAL 1 DAY);

쿼리 해설

• a: 모든 고유한 접속 기록(기기 + 날짜).
• b: a의 각 레코드에 대해, 동일 기기가 다음날 실제로 접속했는지를 확인하는 서브셋.
• LEFT JOIN을 사용하므로 a의 모든 레코드는 유지되며, b는 조건에 맞는 경우만 매칭됨.
• COUNT(b.device_id)는 내일 다시 접속한 횟수이며, COUNT(a.device_id)는 전체 접속 이벤트 수.
• 비율은 ROUND(..., 4)로 소수점 넷째 자리까지 반올림.

연관 문제: 최초 로그인 기준 익일 재방문률

LeetCode 550번 문제처럼, **첫 로그인 날짜 기준으로 다음날 재접속 여부**를 판단해야 하는 경우도 있다. 이때는 먼저 각 사용자의 첫 방문일을 추출해야 한다.

SELECT 
    ROUND(COUNT(t2.player_id) / COUNT(t1.player_id), 2) AS fraction
FROM 
    (SELECT player_id, MIN(event_date) AS first_login 
     FROM activity 
     GROUP BY player_id) t1
LEFT JOIN 
    (SELECT DISTINCT player_id, event_date FROM activity) t2
ON 
    t1.player_id = t2.player_id 
    AND t2.event_date = DATE_ADD(t1.first_login, INTERVAL 1 DAY);

• t1: 각 사용자의 첫 로그인 날짜를 MIN(event_date)로 집계.
• t2: 실제 활동 기록에서 고유한 로그인 정보 추출.
• 조건: t2.event_date가 t1.first_login + 1일과 일치해야 함.
• 결과는 전체 사용자 수 대비, 첫 접속 다음날 다시 온 사용자 비율로 계산되며, 소수점 둘째 자리까지 반올림.