Game Play Analysis IV
Problem
Return the fraction of players who logged back in exactly 1 day after their first login.
rows = [(1,2,'2016-03-01',5),(1,2,'2016-03-02',6),(2,3,'2017-06-25',1),(3,1,'2016-03-02',0),(3,4,'2018-07-03',5)]0.33SELECT ROUND(COUNT(DISTINCT a.player_id) / COUNT(DISTINCT b.player_id), 2) AS fraction
FROM Activity a
RIGHT JOIN (SELECT player_id, MIN(event_date) d FROM Activity GROUP BY player_id) b
ON a.player_id = b.player_id AND a.event_date = DATE_ADD(b.d, INTERVAL 1 DAY);Explanation
This asks for day-1 retention: of all players, what fraction came back the very next day after their first login? The answer is a simple ratio, but building it needs two pieces.
The subquery aliased b finds each player's first login with MIN(event_date). We then RIGHT JOIN the full Activity table a onto it, matching only when a.event_date = DATE_ADD(b.d, INTERVAL 1 DAY) — that is, when the player has activity exactly one day after their first login.
Using a RIGHT JOIN keeps every player in b, even ones who never returned (their a side is NULL). So COUNT(DISTINCT b.player_id) is the total player count, while COUNT(DISTINCT a.player_id) counts only those who matched the next-day rule.
Dividing the two and rounding gives the retention fraction.
Example: only 1 of 3 players logs in the day after their first login, so the result is 1 / 3 ≈ 0.33.