Interview Classics · MIN first activity · LEFT JOIN on date + 1
11. Day-1 return rate
Concept: MIN first activity · LEFT JOIN on date + 1show
Day-1 retention asks whether a user came back exactly one day after their first activity. Find each user's first date with MIN, then LEFT JOIN their activity on the same user and date = first_date + 1 (DATE + 1 is the next day in Postgres). The LEFT JOIN keeps users who never returned, and deduplicating activity first stops a busy day from counting twice.
Example (different data):
WITH f AS ( SELECT player_id, MIN(played_on) AS first_day FROM games GROUP BY player_id ) SELECT player_id, first_day, first_day + 7 AS day_7 FROM f;
Your task
Classic interview question: the mobile team tracks day-1 retention, the share of new users who open the app again the day after their very first open, per monthly cohort.
Group users by cohort_month, the month (YYYY-MM) of their first app open. For each cohort return cohort_month, users (users in the cohort), returned_day_1 (users who also opened the app on the day right after their first open) and day_1_rate (returned_day_1 as a percentage of users, rounded to 2 decimals). Include cohorts where nobody returned. Sort by cohort_month.
Starting Postgres in your browser…
Checking your access…