550. Game Play Analysis IV : leetcode

날짜 차이가 1년 미만인 레코드를 구하려면?

WITH relogin AS (SELECT  a1.player_id, a1.event_date as e1, a2.event_date as e2
                FROM    Activity a1 INNER JOIN Activity a2
                        ON  a1.player_id = a2.player_id 
                        AND DATEDIFF(a1.event_date, a2.event_date) <= 1
                        AND a1.event_date > a2.event_date)
                        
SELECT  ROUND(
        count(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM    relogin

이 풀이는 최초 접속일이란 조건이 빠뜨렸다

수정

# 유저별 최초 접속일
WITH firstLogin AS (
    SELECT player_id, MIN(event_date) as first_date
    FROM Activity
    GROUP BY player_id
)


# 1년 이내 재접속한 유저 수
SELECT
    ROUND( 
        COUNT(DISTINCT a.player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity),
        2) as fraction
FROM firstLogin f
LEFT JOIN Activity a
    ON  f.player_id = a.player_id
    AND a.event_date > f.first_date
    AND DATEDIFF(a.event_date, f.first_date) <= 1;