[where & in ] Game Play Analysis IV

 

링크

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

문제

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.

정답

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)

WITH FirstLogin AS (
    SELECT 
        player_id,
        MIN(event_date) AS first_log
    FROM Activity
    GROUP BY player_id
)

SELECT ROUND(COUNT(a.player_id)/COUNT(f.player_id),2) AS fraction
FROM FirstLogin f
LEFT JOIN Activity a
    ON f.player_id = a.player_id
    AND DATE_ADD(f.first_log, INTERVAL 1 DAY) = a.event_date

해설

• where와 in을 활용해서 조건을 맞춘다

새롭게 이해한 내용

• where절에 괄호와 쉼표로 여러 컬럼을 지정할 수 있고, in을 활용해 select 컬럼의 수를 맞춰 같은 조건에 해당하는 컬럼을 맞출 수 있다.