Drill 10 of 12 in Gaming questions
0

Interview Question Bank · Calendar-day offsets · LEFT JOIN · cohort cutoff

10. Day-7 retention by install week

HardGamingQuestion #154

Concept: Calendar-day offsets · LEFT JOIN · cohort cutoffshow

Day-N retention asks whether someone came back on the Nth calendar day after they started, so compare dates, not timestamps: activity::date = start::date + N. Put that condition in a LEFT JOIN's ON clause so people who didn't return still count in the denominator, and count with DISTINCT in case they returned twice that day. Leave out anyone whose day N is after the data ends, since they haven't had the chance to come back yet.

Example (different data):

SELECT date_trunc('month', m.joined_at)::date AS cohort,
  COUNT(DISTINCT m.id) AS members,
  COUNT(DISTINCT v.member_id) AS back_on_day_30
FROM members m
LEFT JOIN visits v ON v.member_id = m.id
  AND v.visited_at::date = m.joined_at::date + 30
WHERE m.joined_at::date + 30 <= DATE '2025-06-30'
GROUP BY 1;

Your task

Skyrift Arena's growth team tracks day-7 retention for every weekly install cohort. The login data runs through 2025-01-31.

Group players by install week (the Monday of the week of installed_at, as a date). A player is retained if they logged in on the calendar date exactly 7 days after their install date. Leave out players whose day 7 is after 2025-01-31. Return cohort_week, players, retained_d7 and d7_retention_pct (retained as a % of players, rounded to 1 decimal). Sort by cohort_week.

Starting Postgres in your browser…

Checking your access…