Day 1 User Retention Rate in SQL

Question

Given an `activity` table (player_id, device_id, event_date, games_played), calculate Day-1 retention: the fraction of players who logged in on the day immediately following their first login date, rounded to 2 decimal places.

Accepted Answer

Optimal Solution:
WITH first_logins AS (
  SELECT 
    player_id, 
    MIN(event_date) AS first_date
  FROM activity
  GROUP BY player_id
)
SELECT 
  ROUND(
    COUNT(a.player_id)::numeric / COUNT(f.player_id),
    2
  ) AS fraction
FROM first_logins f
LEFT JOIN activity a 
  ON f.player_id = a.player_id 
 AND a.event_date = f.first_date + INTERVAL '1 day';

Technical Explanation:
Find each player's first login date via `MIN(event_date)`. LEFT JOIN back to activity on `event_date = first_date + INTERVAL '1 day'`. Divide matching retention by total players.

player_id	event_date
1	2026-03-01
1	2026-03-02
2	2026-06-25
3	2026-07-03
3	2026-07-05