550. Game Play Analysis IV

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.

이 테이블에서 유일한 값으로 이루어진`player_id 와 envet_date는 기본키이다.
이 테이블은 어떤 게임 플레이어의 활동을 보여준다.
각 행은 어느 기기든 언제나 로그아웃 하기 전에 여러 게임을 플레이한 플레이어의 로그인 횟수를 기록합니다.(가능하면 0으로)

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

FLOW
1. 이후 날짜와 바로 전 날짜의 차이가 1이어야 한다.
2. player_id 를 기준으로 날짜 차이를 조회해야한다.
3. 날짜 차이 횟수를 더한값에 전체 플레이어의 수를 나누어야한다.

1.날짜 차이는 event_date와 min(event_date)의 차이로 구한다.

서브쿼리 사용

  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
  );

1단계.

• player_id 기준에서 가장 작은 값을 가져온다.

    SELECT
      A2.player_id,
      MIN(A2.event_date)
    FROM
      Activity A2
    GROUP BY
      A2.player_id

2단계.

• where문에서 event_date에서 하루 뺀 값이 MIN(A2.event_date)과 같은 경우의 조건만 가져온다.

FROM
  Activity A1
WHERE
  (A1.player_id, DATE_SUB(A1.event_date, INTERVAL 1 DAY)) IN (

3단계. 통합

• 아래 최소 날짜와 현재날짜에서 1 뺀것이 동등한것의 개수와 중복없는 player_id 나눈 값을 조회한다.

  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
  );

---

with문 사용

WITH first_logins AS (
  SELECT
    A.player_id,
    MIN(A.event_date) AS first_login
  FROM
    Activity A
  GROUP BY
    A.player_id
), consec_logins AS (
  SELECT
    COUNT(A.player_id) AS num_logins
  FROM
    first_logins F
    INNER JOIN Activity A ON F.player_id = A.player_id
    AND F.first_login = DATE_SUB(A.event_date, INTERVAL 1 DAY)
)
SELECT
  ROUND(
    (SELECT C.num_logins FROM consec_logins C)
    / (SELECT COUNT(F.player_id) FROM first_logins F)
  , 2) AS fraction;

1단계.

• 최소 날짜를 player_id를 기준으로 가져온다.

WITH first_logins AS (
  SELECT
    A.player_id,
    MIN(A.event_date) AS first_login
  FROM
    Activity A
  GROUP BY
    A.player_id
),

2단계.

• 현재 날짜와 최소날짜의 1차이의 조건을 생성한다.

  SELECT
    COUNT(A.player_id) AS num_logins
  FROM
    first_logins F
    INNER JOIN Activity A ON F.player_id = A.player_id
    AND F.first_login = DATE_SUB(A.event_date, INTERVAL 1 DAY)

3단계. 통합

• consec_logins에서 가져온 날짜를 count한 값과 player_id의 개수를 나눈다.

WITH first_logins AS (
  SELECT
    A.player_id,
    MIN(A.event_date) AS first_login
  FROM
    Activity A
  GROUP BY
    A.player_id
), consec_logins AS (
  SELECT
    COUNT(A.player_id) AS num_logins
  FROM
    first_logins F
    INNER JOIN Activity A ON F.player_id = A.player_id
    AND F.first_login = DATE_SUB(A.event_date, INTERVAL 1 DAY)
)
SELECT
  ROUND(
    (SELECT C.num_logins FROM consec_logins C)
    / (SELECT COUNT(F.player_id) FROM first_logins F)
  , 2) AS fraction;

with와 window함수 사용

WITH cte_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 cte_login

1번째 단계

• player_id를 기준으로 event_date가 1차이나는 값을 가져온다.

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

2번째 단계 통합

• 1차이 나는 횟수를 합한 값과 player_id 3개를 나눈다.

WITH cte_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 cte_login