[LeetCode][SQL50] #22. Game Play Analysis IV

💻 문제 주소 : Game Play Analysis IV - LeetCode

Game Play Analysis IV - LeetCode

💻 문제

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.

Input : Activity table 예시

Output 예시

 

💻 코드 미리보기

select round(count(distinct a1.player_id) / (select count(distinct player_id) from Activity), 2) as fraction 
from Activity a1
    join Activity a2 
    on a1.player_id = a2.player_id and datediff(a2.event_date, a1.event_date) = 1 
where (a1.player_id, a1.event_date) in (select player_id, min(event_date) from Activity group by player_id)

 

💻 문제 풀이

 위 문제는 다음과 같은 조건을 가지고 있다.

1️⃣ 첫 번째 로그인 날과 두 번째 로그인 날이 하루 차이로 연속적일 것
2️⃣ 1️⃣조건에 해당하는 데이터 비율 구하기
3️⃣ 2️⃣에서 구한 비율을 소숫점 두 자리까지 출력

 

1️⃣ 첫 번째 로그인 날과 두 번째 로그인 날이 하루 차이로 연속적일 것

이 문제의 핵심 조건으로, 연속으로 로그인한 사람의 id를 구해야 한다. 이를 위해서 우리는 로그인한 날 간의 차이를 구할 수 있는 함수들 중 datediff() 함수를 사용할 것이다.

 

datediff(date2, date1) 함수는 (date2 - date1) 값을 반환하는 함수로, 위 문제에서는 '연속'으로 로그인하는 것을 조건으로 걸었기 때문에 이 차이가 1이 되어야 한다. 또한 '한 사람'이 같은 id로 연속 로그인해야 하기 때문에 player_id 또한 같아야 한다.

select
from Activity a1
    join Activity a2 
    on a1.player_id = a2.player_id and datediff(a2.event_date, a1.event_date) = 1

 

2️⃣ 1️⃣조건에 해당하는 데이터 비율 구하기

위 self join이 실행되면 Activity 테이블에는 연속으로 로그인한 사람의 데이터만 남게 된다. 따라서 차지 비율을 구하기 위한 '전체' 사람의 수는 구할 수 없기 때문에 전체 사람의 수를 따로 추출해줘야 한다.

select count(distinct a1.player_id) / (select count(distinct player_id) from Activity)
from Activity a1
    join Activity a2 
    on a1.player_id = a2.player_id and datediff(a2.event_date, a1.event_date) = 1

 

3️⃣ 2️⃣에서 구한 비율을 소숫점 두 자리까지 출력

이제 Leetcode에서 익숙한 소숫점 조건이 나왔다. 지금까지 해왔던 대로 round() 함수를 통해 두 자리까지 출력하고 fraction으로 이름을 바꾸어 주면 된다.

select round(count(distinct a1.player_id) / (select count(distinct player_id) from Activity), 2) as fraction
from Activity a1
    join Activity a2 
    on a1.player_id = a2.player_id and datediff(a2.event_date, a1.event_date) = 1

 

여기까지 하면 결과는 아래와 같이 오답으로 채점된다.

Test case2 채점 결과

 

해결점은 바로 1️⃣에 있다.  

 

문제에서도 한 차례 언급했듯, fraction = logged in again on the day after the day they first logged in 으로, 첫 번째 로그인을 한 이후 다음 날을 fraction이라고 한다. Test case2에서 입력된 데이터 중에는 첫째 날부터 셋째 날까지 연속적으로 로그인을 한 케이스가 있는데 이 경우, 첫째-둘째 날만 카운트를 해야 함에도 불구하고 둘째-셋째 날도 카운트를 하기에 Expected 값보다 더 많은 값이 나온 것이다.

 

따라서 우리는 '첫 번째'로 로그인한 날을 기준으로 삼기 위해 Join으로 만든 테이블에 where로 조건을 걸 것이다.

select round(count(distinct a1.player_id) / (select count(distinct player_id) from Activity), 2) as fraction
from Activity a1
    join Activity a2 
    on a1.player_id = a2.player_id and datediff(a2.event_date, a1.event_date) = 1
where (a1.player_id, a1.event_date) 
	in (select player_id, min(event_date) from Activity group by player_id)

 

min() 함수를 넣은 서브쿼리를 통해 각 유저별로 처음 로그인한 날을 구하고, 이들 중 연속적으로 로그인한 유저 id가 있는지를 검토하는 쿼리를 추가하면 비로소 Accepted가 뜨며 끝난다.

 

💻 Beats