Build a cohort retention table in SQL

A cohort is a group of users who share a starting point, usually the month they first did something. A retention table follows each cohort over time and answers a question a single "monthly active users" number hides: do the people we win in one month stay longer than the people we won the month before?

Step 1: The data you need

One table is enough: a user id and the date of an event.

CREATE TABLE events (user_id INTEGER, event_date TEXT);
INSERT INTO events VALUES
 (1, '2025-01-02'), (1, '2025-01-09'), (1, '2025-02-03'),
 (2, '2025-01-05'), (2, '2025-01-06'),
 (3, '2025-01-20'), (3, '2025-02-21'), (3, '2025-03-02'),
 (4, '2025-02-01'), (4, '2025-02-15'), (4, '2025-03-10'),
 (5, '2025-02-11');

Dates are stored as ISO text (YYYY-MM-DD), which sorts correctly and lets substr(date, 1, 7) extract the month.

Step 2: Assign each user to a cohort

A user's cohort is the month of their first event.

WITH first_seen AS (
    SELECT user_id, substr(MIN(event_date), 1, 7) AS cohort_month
    FROM events
    GROUP BY user_id
)
SELECT * FROM first_seen;

Users 1, 2 and 3 belong to 2025-01; users 4 and 5 to 2025-02.

Step 3: Find the months in which each user was active

Reduce the events to one row per user per month, so a user who did ten things in March still counts once.

activity AS (
    SELECT DISTINCT user_id, substr(event_date, 1, 7) AS active_month
    FROM events
)

Step 4: Measure the distance from the cohort month

The offset is the number of months between the cohort month and the active month. Month 0 is the cohort's own month.

joined AS (
    SELECT f.cohort_month, a.user_id,
           (CAST(substr(a.active_month, 1, 4) AS INTEGER) - CAST(substr(f.cohort_month, 1, 4) AS INTEGER)) * 12
         + (CAST(substr(a.active_month, 6, 2) AS INTEGER) - CAST(substr(f.cohort_month, 6, 2) AS INTEGER)) AS months_since
    FROM first_seen f
    JOIN activity a USING (user_id)
)

Multiplying the year difference by 12 keeps the offset right across a new year, for example December to January.

Step 5: Count and divide

Count the distinct users per cohort and offset, and divide by the size of the cohort.

WITH first_seen AS (
    SELECT user_id, substr(MIN(event_date), 1, 7) AS cohort_month
    FROM events GROUP BY user_id
),
activity AS (
    SELECT DISTINCT user_id, substr(event_date, 1, 7) AS active_month FROM events
),
joined AS (
    SELECT f.cohort_month, a.user_id,
           (CAST(substr(a.active_month, 1, 4) AS INTEGER) - CAST(substr(f.cohort_month, 1, 4) AS INTEGER)) * 12
         + (CAST(substr(a.active_month, 6, 2) AS INTEGER) - CAST(substr(f.cohort_month, 6, 2) AS INTEGER)) AS months_since
    FROM first_seen f JOIN activity a USING (user_id)
),
sizes AS (
    SELECT cohort_month, COUNT(*) AS cohort_size FROM first_seen GROUP BY cohort_month
)
SELECT j.cohort_month, s.cohort_size, j.months_since,
       COUNT(DISTINCT j.user_id) AS active_users,
       ROUND(100.0 * COUNT(DISTINCT j.user_id) / s.cohort_size, 1) AS retention_pct
FROM joined j
JOIN sizes s USING (cohort_month)
GROUP BY j.cohort_month, s.cohort_size, j.months_since
ORDER BY j.cohort_month, j.months_since;

Step 6: Read the result

Read it across a cohort: of the three January users, two were back in February and one in March. Read it down a column to compare cohorts at the same age: 66.7% of January's users returned after one month against 50.0% of February's. With only a handful of users that difference means nothing; with thousands it would be worth investigating.

Common mistakes

• Mixing definitions. Count activity the same way for every cohort and every month.
• Reading small cohorts as trends. Percentages of ten users swing wildly. Show the cohort size next to every percentage.
• Counting events instead of users. The DISTINCT is the point: a retention table counts people, not actions.
• Forgetting time zones. Decide whether a month is in UTC or the user's local time, and apply the same rule when the cohort and the activity month are derived.