[LeetCode Medium] 550. 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.

 

 

결과 예시

 

 

정답 코드 (MySQL)

WITH check_login 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 check_login;

 

 

문제 풀이

1. PARTITON BY

문제에서 처음 로그인한 다음 날에 다시 로그인한 플레이어의 비율을 보고자하므로

DATEDIFF 함수를 통해 두 날짜 차이가 1이 되는 행을 찾아야한다.

이때 player_id 별로 로그인 날짜 차이 여부를 확인해야 하므로, PARTITION BY를 사용해 계산하도록 한다.

 

PARTITON BY	GROUP BY
레코드가 줄어들지 않고 행을 집약한 결과를 보여주지 않는다. 즉, 모든 데이터를 다 보여줌	group에 따라 행을 집약한 결과를 보여준다. 집계 함수가 데이터를 하나로 합쳐주는 과정에서, 기존의 상세 데이터들을 잃게 된다.

 

 

GROUP BY를 사용했을 때의 코드와 결과

SELECT player_id, 
        DATEDIFF(event_date, MIN(event_date)) = 1 as login
FROM Activity
GROUP BY player_id;

 

 

 

PARTITION BY를 사용했을 때의 코드와 결과

SELECT player_id, 
        DATEDIFF(event_date, MIN(event_date) OVER(PARTITION BY player_id)) = 1 as login
FROM Activity;

 

 

위 결과를 통해 GROUP BY와 PARTITION BY의 차이를 확실히 확인할 수 있다.

 

2. WITH 절

나의 경우, PARTITION BY를 사용하여 날짜 차이를 구하는 쿼리를 WITH 절에 넣어주었다.

WITH 절을 사용하면, 하나 이상의 서브쿼리에서 반환된 데이터를 단일 쿼리에서 재사용할 수 있다.

이를 통해 코드의 중복을 줄이고, 쿼리의 가독성을 향상시킬 수 있다는 장점이 존재한다.

 

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

 

3. ROUND() 함수

소수점 이하 2자리로 반올림하여 나타내라는 문제 조건이 있었으므로 ROUND() 함수를 사용해준다.

check_login에서 login 컬럼 값들의 합과 player_id 수를 나눠주면된다.

SELECT ROUND(SUM(login) / COUNT(DISTINCT player_id), 2) as fraction
FROM check_login;

 

참고로 문제에서 " 첫 번째 로그인일부터 이틀 이상 연속으로 로그인한 플레이어의 수를 센다 "라고 했는데

아래와 같이 3일 연속으로 로그인을 했다고 하더라도,

player_id	device_id	event_date	games_played
1	2	2016-03-01	5
1	2	2016-03-02	6
1	2	2016-03-03	4
2	3	2017-06-25	1
3	1	2016-03-02	0
3	4	2018-07-03	5

 

DATEDIFF에서 DATEDIFF(event_date, MIN(event_date) OVER(PARTITION BY player_id))

유저 별, 최초 로그인한 날짜를 기준으로 모든 로그인 날짜들과의 차이를 구하는 것이기 때문에

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

 

마지막 SELECT문을 통해 조회할 때

ROUND(SUM(login) / COUNT(DISTINCT player_id), 2) 형태로 값을 구해도 문제가 없게 된다.