How to build a cohort retention query in SQL

A cohort retention table groups users by when they joined and shows what share of each group was still active one month later, two months later, and so on. It is the clearest picture of whether a product keeps people, and the query is the most involved of the common ones.

What you are building

A triangle. Each row is a signup month, each column is months elapsed, each cell is the share of that cohort still active.

It is a triangle because the September cohort has not lived long enough to fill the later columns. Those cells are empty, not zero, and confusing the two is the most common way to misread the chart.

The query

Two building blocks: each user’s cohort, and each month they were active.

PostgreSQL

WITH cohorts AS (
  SELECT
    id AS user_id,
    DATE_TRUNC('month', created_at) AS cohort_month
  FROM users
),
activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', created_at) AS active_month
  FROM events
)
SELECT
  c.cohort_month,
  (EXTRACT(YEAR  FROM a.active_month) - EXTRACT(YEAR  FROM c.cohort_month)) * 12
  + (EXTRACT(MONTH FROM a.active_month) - EXTRACT(MONTH FROM c.cohort_month))
    AS months_since_signup,
  COUNT(DISTINCT a.user_id) AS active_users,
  ROUND(
    100.0 * COUNT(DISTINCT a.user_id)
    / MAX(COUNT(DISTINCT a.user_id)) OVER (PARTITION BY c.cohort_month),
    1
  ) AS retention_pct
FROM cohorts c
JOIN activity a ON a.user_id = c.user_id
WHERE a.active_month >= c.cohort_month
GROUP BY c.cohort_month, 2
ORDER BY c.cohort_month, 2;

The window function is the trick worth stealing. MAX(COUNT(...)) OVER (PARTITION BY cohort_month) gives the size of each cohort at month zero, which is the denominator every cell in that row divides by. It avoids a second pass over the data to compute cohort sizes separately.

MySQL 8.0

Same structure. TIMESTAMPDIFF replaces the month arithmetic:

WITH cohorts AS (
  SELECT id AS user_id, DATE_FORMAT(created_at, '%Y-%m-01') AS cohort_month
  FROM users
),
activity AS (
  SELECT DISTINCT user_id, DATE_FORMAT(created_at, '%Y-%m-01') AS active_month
  FROM events
)
SELECT
  c.cohort_month,
  TIMESTAMPDIFF(MONTH, c.cohort_month, a.active_month) AS months_since_signup,
  COUNT(DISTINCT a.user_id) AS active_users,
  ROUND(
    100.0 * COUNT(DISTINCT a.user_id)
    / MAX(COUNT(DISTINCT a.user_id)) OVER (PARTITION BY c.cohort_month),
    1
  ) AS retention_pct
FROM cohorts c
JOIN activity a ON a.user_id = c.user_id
WHERE a.active_month >= c.cohort_month
GROUP BY c.cohort_month, months_since_signup
ORDER BY c.cohort_month, months_since_signup;

Window functions and CTEs both require MySQL 8.0. On 5.7 this query needs rewriting with a self-join to compute cohort sizes, which is one of the better reasons to upgrade.

Reading it without fooling yourself

The last column is always a lie

The most recent cohort has only been observed for a few days of its first month, and the most recent column of every row is partial. Both look like a sudden decline. Exclude the current month from the chart, or mark it clearly as incomplete.

Month 0 is not always 100%

It is 100% here because the cohort is defined by signup and signup generates an event. If you define cohorts by first purchase and measure activity by logins, month 0 can be lower than 100%, and the whole table means something different. Keep the two definitions deliberately aligned.

A flattening curve is the thing to look for

Retention that declines and then flattens means you have found a group that keeps using the product. Retention that keeps declining in a straight line means you have not. The absolute numbers matter far less than whether the curve ever goes horizontal.

Comparing cohorts of very different sizes

A cohort of 40 users from a quiet month will swing wildly month to month, purely because one person is 2.5%. Show cohort size next to the percentages so nobody reads noise as a trend.

Making it faster

Both CTEs scan a full table and deduplicate. On a large events table this gets slow quickly, and the usual fix is a precomputed user_active_months table refreshed nightly, which turns the whole thing into a small join. Build that when the query is actually too slow, not in advance.

Or just ask for it

This query assumes users sign up in users.created_at, that activity lives in events, and that any event counts as active. Those three assumptions are the entire meaning of the chart, and they differ in every company.

ByeSQL reads your schema and writes the query against it, so you can ask for monthly cohort retention and get the result with the SQL beside it, which is what lets you check the assumptions rather than inherit them.

→ How to calculate daily active users in SQL