Case 2 — Weekly Active Users and 4-Week Retention

# Case 2 — Weekly Active Users and 4-Week Retention > *"From the `events` table, give me weekly active users (WAU) for the last 12 weeks. Then build the 4-week retention curve — for each weekly cohort, what % were still active 1, 2, 3, 4 weeks later?"* Two questions. The first is a warm-up. The second is the real interview filter. --- ## Schema ``` events(user_id, event_time, event_type) ``` `event_time` is a timestamp. Anything in the events table counts as "active". ## Part 1 — WAU for the last 12 weeks ```sql SELECT DATE_TRUNC('week', event_time) AS week_start, COUNT(DISTINCT user_id) AS wau FROM events WHERE event_time >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '12 weeks' AND event_time < DATE_TRUNC('week', CURRENT_DATE) GROUP BY 1 ORDER BY 1; ``` Things to call out: - `DATE_TRUNC('week', ...)` starts on Monday in PostgreSQL by default. - `DISTINCT user_id` is critical — without it you'd count events, not users. - The strict `<` upper bound excludes the in-progress current week. 12 lines of SQL. The interviewer is checking baseline competence, not depth. ## Part 2 — 4-week retention by cohort This is the meat. Build it step by step. ### Step a — Define each user's cohort week (their first event week) ```sql WITH cohorts AS ( SELECT user_id, DATE_TRUNC('week', MIN(event_time)) AS cohort_week FROM events GROUP BY user_id ) SELECT * FROM cohorts; ``` ### Step b — Mark each user's activity weeks ```sql WITH cohorts AS (...), activity AS ( SELECT e.user_id, c.cohort_week, DATE_TRUNC('week', e.event_time) AS activity_week, EXTRACT(WEEK FROM AGE(DATE_TRUNC('week', e.event_time), c.cohort_week))::int AS weeks_since_cohort FROM events e JOIN cohorts c ON c.user_id = e.user_id WHERE e.event_time >= c.cohort_week ) SELECT * FROM activity; ``` `weeks_since_cohort = 0` is the cohort week itself, `1` is one week later, etc. ### Step c — Count active users per cohort × weeks_since_cohort ```sql WITH cohorts AS (...), activity AS (...), retention AS ( SELECT cohort_week, weeks_since_cohort, COUNT(DISTINCT user_id) AS active_users FROM activity WHERE weeks_since_cohort BETWEEN 0 AND 4 GROUP BY cohort_week, weeks_since_cohort ) SELECT * FROM retention; ``` ### Step d — Divide by cohort size ```sql WITH cohorts AS (...), activity AS (...), retention AS (...), cohort_size AS ( SELECT cohort_week, COUNT(*) AS size FROM cohorts GROUP BY cohort_week ) SELECT r.cohort_week, r.weeks_since_cohort, r.active_users, cs.size AS cohort_size, ROUND(100.0 * r.active_users / cs.size, 1) AS retention_pct FROM retention r JOIN cohort_size cs ON cs.cohort_week = r.cohort_week ORDER BY r.cohort_week, r.weeks_since_cohort; ``` ### Step e — Pivot into the triangle shape (optional) ```sql SELECT cohort_week, cohort_size, MAX(CASE WHEN weeks_since_cohort = 0 THEN retention_pct END) AS w0, MAX(CASE WHEN weeks_since_cohort = 1 THEN retention_pct END) AS w1, MAX(CASE WHEN weeks_since_cohort = 2 THEN retention_pct END) AS w2, MAX(CASE WHEN weeks_since_cohort = 3 THEN retention_pct END) AS w3, MAX(CASE WHEN weeks_since_cohort = 4 THEN retention_pct END) AS w4 FROM GROUP BY cohort_week, cohort_size ORDER BY cohort_week; ``` Most interviewers don't ask for the pivot — long format is more useful and most BI tools pivot for you. Mention you could pivot if needed. ## Tradeoffs to mention out loud - **Active = any event** — broad definition. Some shops define active as "made >1 event" or "performed a key action". Confirm before building. - **Right-censoring** — the most recent cohorts haven't had 4 full weeks yet. Their later columns are blank. Either exclude them or annotate. - **Performance** — for huge events tables, scan the events table once. The 4-CTE chain above does exactly that. ## Common follow-ups ### Follow-up 1: "What if 'active' means 'completed at least one key action'?" Filter the `activity` CTE: ```sql activity AS ( SELECT ... FROM events e JOIN cohorts c ... WHERE e.event_type = 'key_action' AND e.event_time >= c.cohort_week ) ``` Note: cohort definition still uses any event (to define week 0). Only the activity check filters. ### Follow-up 2: "Break out retention by acquisition channel" Join in a `users(user_id, signup_channel)` table and add `signup_channel` to the GROUP BY at each step. The output is one retention curve per channel. ### Follow-up 3: "What's the lift from a feature launched in week 5? Compare cohorts before and after." Define "before" = cohorts in weeks 1-4, "after" = cohorts in weeks 6-9, exclude week 5 (the launch week). Average retention by week-since-cohort for each group, compare. ## Self-check MCQs