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

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)

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

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

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

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)

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 <previous query>
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:

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