[leetcode] 550. Game Play Analysis IV/ 이틀 연속 로그인한 유저 /LEFT JOIN/IN

550. Game Play Analysis IV

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

Game Play Analysis IV - LeetCode

 

Write an SQL query 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.

 

처음 로그인한 날 다음 날 다시 로그인한 플레이어의 비율을 소수점 둘째 자리에서 반올림하여 보고합니다. 즉, 첫 로그인 날짜부터 연속 2일 이상 로그인한 플레이어 수를 세고 그 수를 총 플레이어 수로 나누어야 합니다.

 

# 1
SELECT ROUND(COUNT(tab2.player_id)/COUNT(tab1.player_id), 2) AS fraction
FROM (SELECT player_id, MIN(event_date) first_log -- 유저별 첫번째 접속일
        FROM Activity
        GROUP BY player_id) tab1 
        
    LEFT JOIN Activity tab2 
    ON tab1.player_id = tab2.player_id 
        AND tab1.first_log = tab2.event_date - 1

# 2
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)

시간, 날짜 빼기
DATE_SUB(기준 날짜, INTERVAL 1 day)