Day-1 retention in SQL — the share of signups active the next day
Table signups(user_id, signup_date) has one row per user. Table activity(user_id, activity_date) has one row per active user-day. Return a single percentage — the share of signups that had any activity exactly one day after signing up (day-1 retention), rounded to two decimals.
-- one number: day-1 retention as a percent of all signupsWrite the query.
LEFT JOIN activity on activity_date = signup_date + 1, then divide the distinct matched users by all signups. The LEFT JOIN keeps non-returners in the denominator, so the ratio is retained users over the whole signup cohort, not over returners only.
- ✗Using INNER JOIN, which drops non-returners from the denominator
- ✗Counting any later activity instead of the exact next day
- ✗Dividing activity rows instead of distinct users
- →How would you extend this to day-7 or day-30 retention?
- →Why does an INNER JOIN silently change the denominator here?
Match each signup to activity on the day after signup, keeping every signup with a LEFT JOIN so users who never came back still count in the denominator:
SELECT ROUND(
100.0 * COUNT(DISTINCT a.user_id) / COUNT(DISTINCT s.user_id),
2) AS d1_retention_pct
FROM signups s
LEFT JOIN activity a
ON a.user_id = s.user_id
AND a.activity_date = s.signup_date + 1;s.signup_date + 1 is date arithmetic (a date plus an integer is the next date). COUNT(DISTINCT a.user_id) counts signups who were active on day 1; the LEFT JOIN keeps all signups so the denominator is the full cohort. An INNER JOIN would collapse the denominator to returners only and report a meaningless ~100%.
