[LeetCode] 550. Game Play Analysis IV (AGGREGATE)

🗂️  문제

[SQL50] 550. Game Play Analysis IV (AGGREGATE)

---

---

✍🏻  풀이

 

문제 해석

유저가 첫 로그인한 다음날 다시 로그인한 비율을 구하는 문제

 

📌  Table : Activity

Column Name	Type	설명
player_id	int	유저의 고유 ID
device_id	int	유저가 로그인한 장치의 ID
event_date	date	로그인 날짜
games_played	int	해당 로그인 동안 플레이한 게임 수 (0일 수도 있음)

 

• PRIMARY KEY  :  (player_id, event_date)
  • 한 유저(player_id)가 같은 날(event_date)에 여러 번 로그인한 기록은 없다.

 

📌  요구 사항

1. 각 유저의 첫 로그인 날짜(first_login)를 찾는다.
2. 첫 로그인 날짜의 다음날(first_login + 1일)에도 로그인한 유저를 찾는다.
3. 이러한 유저 수를 전체 유저 수로 나누어 비율을 구한다.
4. 결과를 소수점 둘째 자리까지 반올림(ROUND(..., 2)) 한다.

 

 

코드

-- 첫 로그인 날짜를 찾는 CTE
WITH first_logins AS (
    SELECT player_id, MIN(event_date) AS first_login  -- 각 플레이어의 첫 로그인 날짜를 가져옴
    FROM Activity
    GROUP BY player_id
),

-- 첫 로그인 다음날 다시 로그인한 플레이어를 찾는 CTE
next_day_logins AS (
    SELECT A.player_id  -- 첫 로그인 다음날 접속한 플레이어 ID 가져옴
    FROM first_logins A
    JOIN Activity B 
    ON A.player_id = B.player_id 
    AND B.event_date = A.first_login + INTERVAL '1 DAY'  -- 첫 로그인 다음날 접속한 경우만 선택
)

-- 첫 로그인 다음날 다시 접속한 플레이어 비율 계산
SELECT ROUND(COUNT(next_day_logins.player_id) * 1.0 / COUNT(first_logins.player_id), 2) AS fraction
FROM first_logins
LEFT JOIN next_day_logins 
ON first_logins.player_id = next_day_logins.player_id;

 

 

풀이 방식

1. 첫 로그인 날짜 찾기 (CTE : first_logins)
   • player_id 별로 가장 빠른 로그인 날짜(MIN(event_date))를 찾는다.
   • player_id에 대해 첫 로그인 날짜(first_login)을 갖는 임시 테이블이 생성된다.
2. 다음날 로그인한 유저 찾기 (CTE : next_day_logins)
   • first_logins와 Activity를 player_id 기준으로 JOIN 한다.
   • Activity 테이블에서 유저의 로그인 날짜가 첫 로그인 다음날(first_login + 1일)인 경우를 찾는다.
   • 다음날 다시 로그인한 player_id 목록을 얻는다.
3. 다음날 로그인한 유저의 비율 계산
   • first_logins와 next_day_logins를 player_id 기준으로 LEFT JOIN 한다.
   • (1) 첫 로그인 다음날 다시 로그인한 유저 수 : COUNT(next_day_logins.player_id)
   • (2) 전체 유저 수 : COUNT(first_logins.player_id)
   • (1) / (2) 로 비율 계산한 후, ROUND로 소수점 둘째 자리까지 반올림한다.

---

 

 

ROUND

소수점 둘째 자리까지 반올림(ROUND(..., 2)) 하기 위해 사용한다.

https://youngone-kang.tistory.com/51

[SQL] ROUND, CEIL, FLOOR (반올림, 올림, 내림)