Leetcode - 550. Game Play Analysis IV(틀림)

문제

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.

풀이

WITH day_log AS (
    SELECT player_id, MIN(event_date) AS day1, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) AS next_day
    FROM Activity
    GROUP BY player_id
)

SELECT ROUND(CAST(COUNT(*) AS FLOAT) / (SELECT COUNT(*) FROM day_log), 2) AS fraction
FROM day_log a
INNER JOIN Activity b ON b.player_id = a.player_id AND b.event_date = a.next_day

배운점

• WITH문에 INNER JOIN까지 하니까 이해하기 너모 어렵다. 다시 한번 풀어보자