98.Game Play Analysis IV

링크

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.

데이터 이해 및 구상

player_id별로 접속한 날짜(event_date)가 있다.
최초 접속일과 그 다음날에도 연속해서 접속한 유저의 비율을 소수점 2째자리까지 구하여라.

1) 주어진 데이터 중에서는 player_id랑 event_date만 있으면 된다
2) lag() 함수를 사용해서 event_date보다 1일 뒤인 lag_event_date 컬럼을 만들어야 한다
3) 윈도우 함수를 사용할 때 order by event_date asc 넣고 LIMIT 2를 하자. 이를 통해 첫번째 행(=event_date)과 두번째 행(lag_event_date)의 차이가 1이라면 True, 아니면 False를 반환해서 그 수를 세면 된다
4) 이렇게 True가 나온 유저 수를 전체 유저수로 나눠주면 된다

풀이

lag_event_date 계산

먼저 lag() 함수를 활용해서 event_date와 1일 차이나는 날짜를 뽑아준다.
이를 event_date로 order by 해준다 (이후 메인쿼리에서 rownum <= 2만 활용할 예정)

SELECT
    player_id
    , event_date
    , lag(event_date, 1) over (partition by player_id order by event_date) as 'lag_event_date'
    , row_number() over (partition by player_id order by event_date) as 'rownum'
FROM
    Activity

fraction 계산

lag()로 뽑은 값과 Activity 테이블에서 distinct player_id 값을 구해 나누어 주면 된다
전자는 CASE END 구문을 통해 True면 1, False면 0을 반환하도록 하고,
이를 sum을 한다면 True의 개수를 셀 수 있다.

WITH lag_date AS (
SELECT
    player_id
    , event_date
    , lag(event_date, 1) over (partition by player_id order by event_date) as 'lag_event_date'
    , row_number() over (partition by player_id order by event_date) as 'rownum'
FROM
    Activity
)
SELECT
    round((sum(CASE 
            WHEN datediff(event_date, lag_event_date)=1 THEN 1 ELSE 0
        END) 
        / (SELECT count(distinct player_id) FROM activity)) ,2) as 'fraction'
FROM
    lag_date
WHERE
    rownum <= 2