[LeetCode/SQL] 550. Game Play Analysis IV

[LeetCode/SQL]

---

📌 550. Game Play Analysis IV

문제

풀이

각 플레이어가 최초 로그인 날짜를 기준으로 최소 두 번 연속으로 로그인한 경우의 플레이어 수를 계산하고, 이 수를 전체 플레이어 수로 나누어 소수점 둘째 자리까지 반올림한 값을 출력하라

방법 I

SELECT
  ROUND(COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM
  Activity
WHERE
  (player_id, DATE_SUB(event_date, INTERVAL 1 DAY))
  IN (
    SELECT player_id, MIN(event_date) AS first_login FROM Activity GROUP BY player_id
  )

방법 II

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

SELECT ROUND(COUNT(DISTINCT a.player_id) /(SELECT COUNT(player_id) FROM cte),2) AS fraction
FROM Activity AS a
INNER JOIN cte ON a.player_id=cte.player_id 
AND cte.first_login=DATE_SUB(a.event_date, INTERVAL 1 DAY)