550. Game Play Analysis IV

* Problem

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.

 

* 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

 

* Solution (Success)

# Write your MySQL query statement below
WITH
  Players AS (
    SELECT player_id, MIN(event_date) AS first_login
    FROM Activity
    GROUP BY 1
  )
SELECT ROUND(
    COUNT(Players.player_id) / (
      SELECT COUNT(DISTINCT Activity.player_id)
      FROM Activity
    ),
    2
  ) AS fraction
FROM Players
INNER JOIN Activity
  ON (
    Players.player_id = Activity.player_id
    AND DATEDIFF(Players.first_login, Activity.event_date) = -1)

 

최근에는 다른 분의 쿼리를 보며 naming, query 구조를 확인하고 있습니다.

With절에 대한 사용이 생각보다 많았고, 가독성 부분에서도 배워야 할 부분이 참 많구나 하는 생각을 하는 문제 풀이시간이었습니다.

앞으로 쿼리를 짤때 쿼리 코딩 룰을 잘 인지할 것.!