[LeetCode Medium] 550. Game Play Analysis IV(MySQL)

✅ 본 게시글은 학습 목적으로 LeetCode 문제의 풀이과정을 정리한 것이며, 모든 자료의 출처는 LeetCode에 있습니다.

 

Game Play Analysis IV - LeetCode

 

지금까지 푼 리트코드 문제 중에 가장 어려웠던... 정답률 37.5% 문제

그래도 2시간 동안 열심히 고민해서 제출 성공했다 !!!!

 

---

 

LeetCode 550번 문제

 

제일 처음 로그인 한 바로 다음 날 연속으로 로그인 한 유저의 비율을 찾는 문제이다.

 

 

WITH first_login_date AS
(SELECT player_id, MIN(event_date) AS first_login_date
FROM Activity
GROUP BY player_id),

first_login_tbl AS
(SELECT a.player_id, a.device_id, a.event_date, f.first_login_date
FROM Activity a
JOIN first_login_date f
ON a.player_id = f.player_id)

SELECT ROUND(COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM first_login_tbl
WHERE DATEDIFF(event_date, first_login_date) = 1

위 코드는 내가 제출한 코드이다.

WITH문으로 각 유저별로 제일 처음 로그인 한 날짜 컬럼이 포함된 first_login_date 테이블을 만든다.

그리고 이 테이블을 원본 Activity 테이블과 조인해 제일 처음 로그인 한 날짜 컬럼을 아래와 같이 원본 테이블에 붙인다.

 

마지막으로, first_login_date와 event_date의 날짜 차이(DATEDIFF)가 1인 unique 유저들의 비율을 구한다.

이렇게 쿼리를 짜도 올바른 결과가 나오지만 더 깔끔한 다른 사람의 쿼리를 가져와봤다.

 

 

SELECT ROUND(SUM(login)/COUNT(DISTINCT player_id), 2) AS fraction
FROM (
  SELECT
    player_id,
    DATEDIFF(event_date, MIN(event_date) OVER(PARTITION BY player_id)) = 1 AS login
  FROM Activity
) AS t

여기서 새로 배운 점은, 각 유저 아이디별로 제일 처음 로그인 한 날짜를 MIN(event_date) OVER(PARTITION BY player_id)를 통해 구했다는 것이다. 나는 WITH문으로 가상의 테이블을 하나 새로 만들고 조인까지 사용했는데 이 방법보다 훨~~씬 간단하다.

 

아래 테이블로 예시를 들어 좀 더 쉽게 설명해보겠다.

예시 테이블

 

위와 같은 테이블이 있다고 가정했을 때, 서브쿼리를 조금 변형시킨 다음 쿼리를 실행하면

SELECT player_id, event_date, MIN(event_date) OVER(PARTITION BY player_id) AS login
FROM Activity

각 player_id별로 최초 로그인 날짜가 login이라는 이름의 컬럼으로 출력된다!

 

그리고 또 신기한 점은, '최초 로그인 날짜와 event_date의 차이'가 1인 행을 출력할 때 WHERE절을 사용하지 않았다는 것이다.

서브쿼리를 살펴보자.

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

새로 알게된 내용인데, SELECT절에 '조건 = 어떤 값'을 입력하면, '어떤 값'에 해당되는 행은 1로, 해당되지 않는 행은 0으로 출력된다. 예를 들어, 앞서 예시 테이블에서 SELECT player_id = 3을 입력하면, player_id가 3인 행은 1로 출력되고 나머지는 0으로 출력된다.

이 개념을 인지하고 다시 서브쿼리를 살펴보면, login이라는 컬럼에, '최초 로그인 날짜와 event_date의 차이'가 1인 행은 1을 출력하고 그렇지 않은 행은 0을 출력하도록 했다.

 

 

그래서 결국 서브쿼리만 실행하면 다음과 같은 결과가 나온다.

'최초 로그인 날짜와 event_date의 차이'가 1인 행은 두 번째 행밖에 없으므로, 두 번째 행만 1이 나오는 것이다.

 

 

이제 서브쿼리 밖에 있는 쿼리를 살펴보겠다.

SELECT ROUND(SUM(login)/COUNT(DISTINCT player_id), 2) AS fraction

위에서 '최초 로그인 날짜와 event_date의 차이'가 1인 행은 전부 1로 표시되게 했으므로, 이것들을 다 더하면, 즉 SUM(login)을 실행하면, '최초 로그인 날짜와 event_date의 차이가 1인 차이가 1'인 고유 유저 수가 나온다. 그리고 이 숫자를 전체 고유 유저 수로 나누어 비율을 구해준다.