[SQL/분석] D+1 리텐션(Retention) 구하기: 첫 로그인과 재방문의 연결고리 (LeetCode550 Medium)

[리트코드] Medium|550. Game Play Analysis IV; 리텐션

https://leetcode.com/problems/game-play-analysis-iv/description/

 

1. Problem

전체 플레이어 중 **'생애 첫 로그인 바로 다음 날'**에 다시 접속한 플레이어의 비율을 구해야 한다.

• 핵심 과제:
  1. 각 유저의 최초 로그인 날짜를 찾아야 함.
  2. 그 날짜로부터 정확히 1일 뒤에 기록이 있는지 확인해야 함.
  3. 중복 집계를 방지하여 정확한 '인원수' 비율을 계산해야 함.

2. Solution: 최초 방문일 고정 후 조건부 집계

WITH 절로 최초 방문일을 정의하고, 본 테이블과 조인하여 날짜 차이를 분석한다.

3. Takeaway: COUNT(DISTINCT CASE WHEN...)의 정교한 매커니즘 (객관적 분석)

유진 님이 분석하신 대로 이 구문은 리텐션 계산의 '치트키'와 같습니다.

• DATEDIFF의 필터링: A.event_date - F.first_date = 1인 경우만 선별합니다. (D+1 접속자)
• CASE WHEN의 역할: 조건에 맞으면 player_id를, 아니면 NULL을 반환합니다.
• DISTINCT의 중요성: 만약 한 유저가 다음 날 5번 로그인했다면, DISTINCT가 없을 경우 5명으로 카운트되어 비율이 100%를 초과하는 오류가 발생합니다. DISTINCT는 이를 1명으로 정확히 압축합니다.
• 분모 처리의 객관성: 조인된 테이블(A JOIN F)에서 분모를 구하면 데이터 구조에 따라 중복이 발생할 위험이 있습니다. 따라서 (SELECT COUNT(DISTINCT player_id) FROM Activity)와 같이 전체 모수를 서브쿼리로 명확히 정의하는 것이 가장 안전합니다.

---

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
(player_id, event_date) is the primary key (combination of columns with unique values) of this table.
This table shows the activity of players of some games.
Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on someday using some device.

Write a solution to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to determine the number of players who logged in on the day immediately following their initial login, and divide it by the number of total players.

The result format is in the following example.

 

Example 1:

Input: 
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-03-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
Output: 
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
Explanation: 
Only the player with id 1 logged back in after the first day he had logged in so the answer is 1/3 = 0.33

 

 

1. 나의 답안 

WITH first_login AS (
    SELECT player_id, MIN(event_date) AS first_date
    FROM Activity
    GROUP BY player_id
)

SELECT ROUND(COUNT(CASE WHEN DATEDIFF(A.event_date, F.first_date) = 1 THEN 1 ELSE NULL END) / COUNT(DISTINCT A.player_id),2) AS fraction
FROM Activity A
JOIN first_login F ON A.player_id = F.player_id;

 

 

2. 모범 답안

WITH first_login AS (
    SELECT player_id, MIN(event_date) AS first_date
    FROM Activity
    GROUP BY player_id
)

SELECT 
    ROUND(
        -- 분자: 첫 로그인 날짜 + 1일인 기록이 Activity에 존재하는 플레이어 수
        COUNT(DISTINCT CASE WHEN DATEDIFF(A.event_date, F.first_date) = 1 THEN A.player_id END) 
        / 
        -- 분모: 전체 플레이어 수
        (SELECT COUNT(DISTINCT player_id) FROM Activity)
    , 2) AS fraction
FROM Activity A
JOIN first_login F ON A.player_id = F.player_id;

 

COUNT(DISTINCT CASE WHEN DATEDIFF(A.event_date, F.first_date) = 1 THEN A.player_id END)

 

• DATEDIFF 조건 판별: A.event_date가 첫 로그인 날짜(F.first_date)보다 정확히 1일 뒤인지 계산합니다.
• CASE WHEN의 선별:
  • 1일 차이가 맞다면? player_id를 뱉습니다.
  • 아니라면? NULL을 뱉습니다.
• DISTINCT의 중복 제거: 한 플레이어가 D+1에 여러 번 로그인해서 player_id가 여러 개 모였더라도, 딱 하나만 남깁니다.
• COUNT의 집계: 최종적으로 남은 '유니크한 플레이어 ID'의 개수만 세고, NULL은 무시합니다.