[LeetCode] game-play-analysis-iv

https://leetcode.com/problems/game-play-analysis-iv/?envType=study-plan-v2&envId=top-sql-50

Game Play Analysis IV - LeetCode

전체 유저에서 첫 로그인날부터 2일 이상 연속으로 접속한 유저의 비율을 구하는 문제였다.

 

내가 짠 쿼리는 다음과 같다.

SELECT ROUND(COUNT(CASE WHEN a.player_id IS NOT NULL THEN f.player_id ELSE NULL END) / COUNT(f.player_id), 2) AS fraction
FROM (
    SELECT player_id,
        MIN(event_date) AS first_play
    FROM activity
    GROUP BY player_id) AS f
    LEFT JOIN activity a ON f.player_id = a.player_id
        AND DATEDIFF(event_date, first_play) = 1

 

1.유저별 첫 로그인 날짜를 구함

SELECT player_id,
        MIN(event_date) AS first_play
FROM activity
GROUP BY player_id

 

2. 1번 테이블과 activity 테이블을 LEFT JOIN하여 첫 로그인일로부터 하루 차이나는 날짜가 event_date column에 존재하는 경우를 JOIN (DATEDIFF 이용)

** DATEDIFF(day1, day2) --> day1-day2의 차이

SELECT *
FROM (
    SELECT player_id,
        MIN(event_date) AS first_play
    FROM activity
    GROUP BY player_id) AS f
    LEFT JOIN activity a ON f.player_id = a.player_id
        AND DATEDIFF(event_date, first_play) = 1

 

3. 2번까지의 JOIN 결과를 활용하여 계산식 작성하면 최종 쿼리다!

첫 로그인일과 하루 차이나는 일자가 없는 경우 actviity 테이블이 JOIN되지 않았기 때문에 activity 테이블의 player_id가 NULL이므로 이걸 이용해서 CASE 조건문을 작성해주었다.

 

자 이제 Solution을 확인해보았다.

 

1번까지는 동일하나 DATE_SUB 함수를 사용했다.

SELECT *
FROM activity
WHERE (player_id, DATE_SUB(event_date, INTERVAL 1 DAY)) -- event_date 1일 전 = 첫방문일 인 경우
    IN (
    SELECT player_id,
        MIN(event_date) AS first_login
    FROM activity
    GROUP BY player_id
    )

이렇게 하면 내가 2번 접근에서 의도한 바와 동일한 접근이 된다.

** DATE_SUB(<column>, INTERVAL <number> DAY) : column으로부터 number일을 뺀 값

SELECT ROUND(COUNT DISTINCT player_id) / -- WHERE절을 통해 필터링된 유저 아이디 수
    (SELECT COUNT(DISTINCT player_id) FROM activity), 2) AS fraction -- 전체 유저 아이디 수
FROM activity
WHERE (player_id, DATE_SUB(event_date, INTERVAL 1 DAY))
    IN (
    SELECT player_id,
        MIN(event_date) AS first_login
    FROM activity
    GROUP BY player_id
    )

fraction의 분자는 WHERE절을 통해 필터링한 테이블을 사용하고, 분모는 activity 테이블의 player_id를 세는 방식을 사용했다.