[MySQL] LeetCode 문제풀이 : 550. Game Play Analysis IV

문제

난이도 : Medium

SQL Schema > Pandas Schema

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
(player_id, event_date) is the primary key (combination of columns with unique values) of this table.
This table shows the activity of players of some games.
Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on someday using some device.

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.

The result format is in the following example.

Example 1:

Input:
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-03-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
Output:
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
Explanation:
Only the player with id 1 logged back in after the first day he had logged in so the answer is 1/3 = 0.33

 

  

첫 시도

• WITH문에서 사용자 별 event_date에 순서를 매겨주고
• 메인 쿼리에서 2번째 로그인한 사용자의 총 인원을 계산

-- player_id 그룹별 2번째 event_date 값 찾기
WITH logged AS (
    SELECT player_id
        , event_date
        , ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS logged_order
    FROM Activity
)

-- 2번째 로그인 개수
SELECT COUNT(player_id)
    -- , player_id
FROM logged
WHERE logged_order = 2

 

총 플레이어 수와 2번째 이상 로그인한 플레이어 수 각각을 구할 수는 있다.

각각의 테이블이 필요한데 한 쿼리문에서 해결하기가 어렵다.

  

2차 시도

• LEAD() 함수를 활용하여 2번째 방문 날짜가 어떤 식으로 출력되는지 먼저 확인해보자

  SELECT player_id
      , event_date
      , LEAD(event_date, 1) OVER (PARTITION BY player_id ORDER BY event_date) AS second_logged
  FROM Activity

• 전체 사용자 수 : COUNT(player_id)
• 2번째 방문한 사용자 수 : 사용자 별 최소값의 개수를 COUNT (null 제외)

-- player_id 그룹별 2번째 event_date부터 출력하는 컬럼 만들기
WITH logged AS (
    SELECT player_id
        , event_date
        , LEAD(event_date, 1) OVER (PARTITION BY player_id ORDER BY event_date) AS second_logged
    FROM Activity
), player_second AS (
    SELECT player_id
        , MIN(second_logged) AS second
    FROM logged
    WHERE second_logged IS NOT NULL
    GROUP BY player_id
)

SELECT ROUND(COUNT(DISTINCT player_second.second)/COUNT(DISTINCT logged.player_id), 2) AS fraction
FROM logged
LEFT JOIN player_second
ON logged.player_id = player_second.player_id

 

 

결과가 예상과 다르게 나온다.

COUNT 연산을 하지않고, 테이블이 어떻게 출력되는지 확인해본다.

-- player_id 그룹별 2번째 event_date부터 출력하는 컬럼 만들기
WITH logged AS (
    SELECT player_id
        , event_date
        , LEAD(event_date, 1) OVER (PARTITION BY player_id ORDER BY event_date) AS second_logged
    FROM Activity
), player_second AS (
    SELECT player_id
        , MIN(second_logged) AS second
    FROM logged
    WHERE second_logged IS NOT NULL
    GROUP BY player_id
)

SELECT *
FROM logged
LEFT JOIN player_second
ON logged.player_id = player_second.player_id

출력 결과가 아래와 같이 나온다.

COUNT(DISTINCT logged.player_id)는 3이고, COUNT(DISTINCT player_second.second)는 2 라서 0.67이 나왔다.

 

여기까지 풀고나니 갑자기 player_id랑 event_date를 concate 하여 활용해도 좋을 것 같다는 생각이 든다.

  SELECT player_id
      , CONCAT(player_id, '_', event_date) AS player_log
  FROM Activity

쿼리를 실행하면 아래와 같이 player_id와 event_date 컬럼 값들을 하나로 합쳐주어 고유한 값이 된다.

 

그러나 1차 시도와 마찬가지로 전체 사용자 수와 2번 이상 로그인 사용자 수를 한 쿼리문에서 계산하여 비율을 구하는 지점에서 계속 막힌다.

  

3차 시도

SELECT -- 전체 플레이어 중 이틀 연속 로그인한 플레이어 비율 계산
  ROUND(
    COUNT(A1.player_id)
    / (SELECT COUNT(DISTINCT A3.player_id) FROM Activity A3)
  , 2) AS fraction
FROM
  Activity A1
WHERE -- 첫 로그인 후 이틀 연속 로그인한 경우를 필터링
  (A1.player_id, DATE_SUB(A1.event_date, INTERVAL 1 DAY)) IN (
	  -- 플레이어 별 첫 로그인 날짜
    SELECT
      A2.player_id,
      MIN(A2.event_date)
    FROM
      Activity A2
    GROUP BY
      A2.player_id
  );

3-1. 문제 이해부터 잘못되었다.

• 문제 : count the number of players that logged in for at least two consecutive days starting from their first login date
• → 첫 로그인 날짜로부터 이틀 연속 로그인 한 플레이어의 수

즉, 첫 로그인 날짜 바로 다음날에도 로그인 한 플레이어의 수를 구해야 한다.

• WHERE절 에서 self join을 활용해 첫 로그인 정보를 정의한 뒤, 이틀 연속 로그인한 경우를 필터링한다.
  • 플레이어 별 첫 로그인 날짜를 구하는 내부 쿼리 → 다중행 서브쿼리
  • 첫 로그인 후 이틀 연속 로그인한 경우 필터링하는 ( a, b ) IN (다중행 서브쿼리) 구조 → 다중 컬럼 서브쿼리

3-2. self join을 활용하여 전체 사용자 수와 이틀 연속 사용자 수를 각각 구해야 한다.

• WHERE절 의 self join : "현재 검사하고 있는 각 행의 플레이어와 '날짜-1일' = 첫 로그인 플레이어와 날짜"인 경우를 필터링 한다. 즉 현재 검사하고 있는 행의 데이터는 ‘첫 로그인 다음날(+1일)’의 정보가 된다.
• SELECT절의 self join : 전체 플레이어 수를 구하기 위해 사용

  

회고

KEEP

• 다양한 방식으로 접근하여 문제를 풀고자 했다.
• 내가 해결하고자 하는 문제를 작은 단위로 나누고, 단계적으로 정의하여 풀어갔다.

PROBLEM

• 영어로 된 문제를 ‘정확히’ 해석하지 못했다.
• 전체 플레이어 수와 이틀 연속 플레이어 수를 구하기 위한 각각의 테이블이 필요하다는 것은 인지했지만 해결방법을 찾기까지 오래 걸렸다.

TRY

• 모든 문제를 직접 먼저 해석해보고 → 맞는지 구글 번역 돌려보기
• 문제를 풀 때마다, 어떤 문제는 어떤 방식으로 해결할 수 있는지 정리해가면 풀기
• 여러 문제를 풀어보며 다양한 해결방법을 경험하고, 내 것으로 만들어보자!