550. Game Play Analysis IV | Medium💡

문제

 

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 count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players.

테이블 정보

 

전체 유저 중 최초 접속한 날을 기준으로 이틀 연속 접속한 플레이어의 비율을 계산하는 문제이다.

 

Solution

SELECT ROUND(COUNT(a1.player_id)/(SELECT COUNT(DISTINCT a3.player_id) FROM Activity a3), 2) AS fraction
FROM Activity a1
WHERE (a1.player_id, a1.event_date) IN (
    SELECT a2.player_id
         , DATE_ADD(MIN(a2.event_date), INTERVAL 1 DAY) 
    FROM Activity a2
    GROUP BY a2.player_id
)

/* ✅ 더 이상향적인 풀이
WITH step2 AS (
    SELECT *
         , DATEDIFF(next_login, event_date) AS day_diff
    FROM (
        SELECT player_id
             , event_date
             , LEAD(event_date, 1) OVER (PARTITION BY player_id ORDER BY event_date) AS next_login
        FROM Activity
    ) step1
)
SELECT ROUND(COUNT(CASE WHEN day_diff = 1 THEN player_id END) / COUNT(*), 2) AS fraction
FROM step2
WHERE (player_id, event_date) IN ( -- 첫접속 이후의 2틀 연속된 접속 기록이 있을 수 있기 때문
    SELECT player_id
         , MIN(event_date)
    FROM step2
    GROUP BY player_id
)
*/

 

✅ WHERE절에 다중 컬럼 서브쿼리를 활용하여, 첫 접속 이후 다음 날에도 접속한 플레이어를 필터링한다.

✅ SELECT절에 스칼라 서브쿼리를 활용하여, 전체 고유 플레이어 수를 반환한다.