Recap — Building a Retention Table

The output every PM wants:

Cohort	Month 0	Month 1	Month 2	Month 3
Jan 2026	100%	62%	48%	41%
Feb 2026	100%	58%	45%	—
Mar 2026	100%	60%	—	—

It's called the retention triangle. It's pure SQL. Here's the full pattern.

Step 1 — Pin each user to a cohort

WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(event_date)) AS cohort_month
    FROM events
    GROUP BY user_id
)
SELECT * FROM cohorts;

Every user gets one row with their first event month as their cohort.

Step 2 — Compute each user's months-since-signup for every activity month

WITH cohorts AS (
    SELECT user_id, DATE_TRUNC('month', MIN(event_date)) AS cohort_month
    FROM events GROUP BY user_id
),
activity AS (
    SELECT
        c.user_id,
        c.cohort_month,
        DATE_TRUNC('month', e.event_date) AS activity_month,
        EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', e.event_date), c.cohort_month)) AS months_since_signup
    FROM cohorts c
    JOIN events e ON e.user_id = c.user_id
    GROUP BY c.user_id, c.cohort_month, DATE_TRUNC('month', e.event_date)
)
SELECT * FROM activity;

Each row says: "user 7 was in the Jan cohort and was active in month 2 (so months_since_signup = 2)."

Step 3 — Count distinct users per cohort × months-since-signup

WITH cohorts AS (...),
activity AS (...),
retention AS (
    SELECT
        cohort_month,
        months_since_signup,
        COUNT(DISTINCT user_id) AS active_users
    FROM activity
    GROUP BY cohort_month, months_since_signup
)
SELECT * FROM retention;

Step 4 — Divide by cohort size

WITH cohorts AS (...),
activity AS (...),
retention AS (...),
cohort_size AS (
    SELECT cohort_month, COUNT(*) AS size
    FROM cohorts GROUP BY cohort_month
)
SELECT
    r.cohort_month,
    r.months_since_signup,
    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_month = r.cohort_month
ORDER BY r.cohort_month, r.months_since_signup;

You can pivot the final output into the triangle shape using CASE WHEN or in your BI tool. Most analysts leave it in long format and let Looker/Tableau pivot.

What to look for in the triangle

• The Month 1 drop — your activation problem. If it's >70%, your onboarding is broken.
• The curve from Month 1 onward — the shape matters. A flattening curve is healthy. A still-declining curve at month 6 means churn never stabilises.
• The "smile" — some products see retention curve UP after a dip. This is rare and is your strongest signal of product-market fit.
• Compare cohorts — if Feb is meaningfully worse than Jan at the same age, something broke in Feb (acquisition channel, product change, market shift).

Common interview mistakes

1. Forgetting DISTINCT in step 3 — COUNT(*) instead of COUNT(DISTINCT user_id) counts events, not users.
2. Computing retention from activity tables alone — without joining to a cohort definition, you can't normalize.
3. Using a fixed cohort size instead of a per-cohort one — if cohorts vary in size (they do), you have to divide each cohort by its own size.

Takeaway

Memorize the 4-step pattern (cohort → activity → retention → size). In any interview that asks about cohort analysis, you'll have the bones of the query in your head in 30 seconds.