문제

처음 로그인하고 다음날 다시 로그인한 플레이어의 비율을 소수점 이하 2자리로 반올림하여 나타내는 쿼리를 작성해주세요.( 즉, 처음 로그인한 날부터 이틀 이상 연속으로 로그인한 플레이어의 수를 세고, 그 수를 전체 플레이어의 수로 나누어야 합니다.)

테이블 및 예시

코드

처음 시도 했던방법..😂

with event as (
SELECT *
FROM (SELECT player_id
            , event_date
            , LAG(event_date) OVER (partition by player_id order by event_date) as before_date
        FROM Activity) a
            )


select (select count(*)
        from event
        where event_date - before_date = 1) / count(distinct(player_id)) as fraction
from event

위 문제의 key는 어떻게 연속적으로 로그인한 유저를 구하느냐 인데.. 처음 사용했던 방법은 lag 함수를 이용해서 이전의 로그인에 해당하는 before_date 컬럼을 직접 만들고 event_date 와 차이가 1인 유저를 가져오는 방식을 사용했습니다.

player_id	event_date	before_date
1	2016-03-01	null
1	2016-03-02	2016-03-01
2	2017-06-25	null
3	2016-03-02	null
3	2018-07-03	2016-03-02

player_id 가 1번인 경우 딱 한개 있군요.

하지만 위 방법을 이용하고 최종적으로 fraction 값을 구한뒤 반올림하게되면 자꾸 이상한 값으로 변경되는 현상이 나타나더군요.. 반올림 하기전에는 0.3333 값이지만 반올림 하게되면 아래처럼 문제 자체의 output 도 바뀌게 나옵니다 ㅎㅎ; 뭐지

두번째 방법

두번째는 솔루션을 참고한 방법입니다. 저는 lag 함수를 이용해서 그냥 컬럼 자체를 만들어 버렸는데 더 쉬운 방법이 있었어요

WITH EVENT AS (
SELECT player_id
    , DATEDIFF(event_date, MIN(event_date) OVER(PARTITION BY player_id)) = 1 as login
FROM activity)

SELECT round(sum(login) / count(distinct(player_id)),2 ) as fraction
FROM EVENT

어차피 연속적으로 로그인한 사람을 구하는거니까, min()함수를 이용해 최초접속일을 구한 뒤 event_date끼리 빼면 되는거였습니다. 마찬가지로 차이가 1인 경우 다음날 연속으로 로그인한 사람이 됩니다. 아래의 쿼리를 실행하게 되면 boolean 값으로 데이터가 나오고 값을 합치면 연속으로 로그인한 사람수가 됩니다.

DATEDIFF(event_date, MIN(event_date) OVER(PARTITION BY player_id)) = 1 as login

player_id	login
1	0
1	1
2	0
3	0
3	0

역시 연습이 많이 필요하다...🫠