[SQL] 550. Game Play Analysis IV

문제 링크

https://leetcode.com/problems/game-play-analysis-iv/description/

문제 설명

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

내 접근

DAtE(MAX- MIN) 2일 이상 찾아보자

SELECT ROUND(COUNT(CASE WHEN SUB2.YN = 1 THEN 1 END)/COUNT(SUB2.player_id),2) fraction
FROM (SELECT player_id , CASE WHEN MAX_D-MIN_D = 0 THEN 0
WHEN MAX_D-MIN_D <= 2 THEN 1 ELSE 0 END AS YN
FROM (SELECT player_id , MAX(event_date) MAX_D, MIN(event_date) MIN_D
FROM Activity
GROUP BY 1) AS SUB
) SUB2

였는데 문제 이해를 잘못 한 것 같다.
첫번째 로그인 이후 연속해서 로그인 한 사람들의 비율을 뽑아야 한다.

WITH sub1 AS(
SELECT player_id, MIN(event_date) D_MIN, COUNT(player_id) p_count
FROM Activity
GROUP BY 1
)

SELECT round(COUNT(sub1.player_id) / (SELECT COUNT( DISTINCT player_id) FROM Activity),2) as fraction
FROM sub1,Activity a1
WHERE sub1.player_id = a1.player_id AND sub1.D_MIN+1 = a1.event_date

TEST CASE 에 있는 정보들은 전부 통과했는데
제출을 하면 틀렸다는 메세지만 보였고.
확인을 해보았다.

분명 내가 짠 조건에 해당되어 걸려야 하는 부분이
2016-02-29, 2016-03-01 이었는데 2월이라서 매년 날짜가 바뀌니 DateDiff로 바꿔봐보았더니 성공.

수정된 쿼리.

WITH sub1 AS(
SELECT player_id, MIN(event_date) D_MIN, COUNT(player_id) p_count
FROM Activity
GROUP BY 1
)
SELECT round(COUNT(sub1.player_id) / (SELECT COUNT( DISTINCT player_id) FROM Activity),2) as fraction
FROM sub1,Activity a1
WHERE sub1.player_id = a1.player_id 
AND DATEDIFF(a1.event_date,sub1.D_MIN) = 1