Retention rate in SQL: who came back the next month

Why the usual answer is wrong

SELECT ROUND(100.0 * (SELECT COUNT(DISTINCT user_id) FROM activity WHERE active_month = '2026-02')
  / (SELECT COUNT(*) FROM users WHERE signup_month = '2026-01'), 1) AS retention_pct;

User 5 joined in February, so they can't be a returning January user. Counting everyone active in February gives 75% instead of 50%.

The answer

SELECT ROUND(100.0 * COUNT(DISTINCT a.user_id) / COUNT(DISTINCT u.user_id), 1) AS retention_pct
FROM users u
LEFT JOIN activity a
  ON a.user_id = u.user_id AND a.active_month = '2026-02'
WHERE u.signup_month = '2026-01';

Start from the January group (the cohort), then look for their February activity. Both the top and the bottom of the fraction are January users.

COUNT(DISTINCT a.user_id) ignores the NULLs from users with no February activity, which is exactly the count of who came back.