Drill 11 of 12 in Interview Classics
0

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…