Cohort and Retention Analysis in SQL: A Step-by-Step Guide

Cohort retention analysis in SQL groups users by when they started, then measures what share of each group is still active in later periods. Instead of one blended retention number that mixes old and new users, you get a retention matrix that shows whether newer cohorts retain better or worse than older ones. This step-by-step guide builds that matrix in PostgreSQL, explains how to read it, and compares N-day, unbounded, and bracket retention.

What is cohort analysis?

A cohort is a group of users who share a starting event in the same time window, most often their signup or first purchase month. Cohort analysis follows each group forward in time and compares them at the same age, such as month 1 or day 30.

Why not just track overall active users? Because overall numbers mix everyone together. A growth spike of new users can hide the fact that each new cohort churns faster than the last. Cohorts separate "how many users arrived" from "how well we keep them".

Step 1: Define the cohort and the activity

Two decisions shape every retention analysis:

• Cohort event: what puts a user into a cohort. Signup date is common; first purchase is better for commerce questions.
• Activity event: what counts as retained. Logging in is easy to measure but weak; a meaningful action, such as placing an order or completing a lesson, is usually a better signal.

The examples below use two tables:

users  (user_id, signup_at timestamptz)
events (user_id, event_name text, event_at timestamptz)

Decide your time zone before anything else. Truncating timestamps in UTC versus local time can move users across day or month boundaries and change the results.

Step 2: Build the cohort and activity tables

Start with one row per user for the cohort, and one row per user per active period for activity. Deduplicating activity with DISTINCT is important: a user who places ten orders in a month should count once for that month.

WITH cohorts AS (
  SELECT
    user_id,
    date_trunc('month', signup_at)::date AS cohort_month
  FROM users
),
activity AS (
  SELECT DISTINCT
    user_id,
    date_trunc('month', event_at)::date AS activity_month
  FROM events
  WHERE event_name = 'order_completed'
)
SELECT * FROM activity LIMIT 10;

If your cohort is based on first activity instead of signup, derive it with MIN(activity_month) per user, or with a window function such as MIN(activity_month) OVER (PARTITION BY user_id). See SQL window functions explained for how that works.

Step 3: Write the retention matrix query

Join activity to cohorts, compute how many periods after the cohort start each activity happened, and divide active users by cohort size. The month number is the difference in months between the activity month and the cohort month:

WITH cohorts AS (
  SELECT
    user_id,
    date_trunc('month', signup_at)::date AS cohort_month
  FROM users
),
activity AS (
  SELECT DISTINCT
    user_id,
    date_trunc('month', event_at)::date AS activity_month
  FROM events
  WHERE event_name = 'order_completed'
),
cohort_size AS (
  SELECT cohort_month, COUNT(*) AS users
  FROM cohorts
  GROUP BY cohort_month
),
cohort_activity AS (
  SELECT
    c.cohort_month,
    a.user_id,
    ((EXTRACT(YEAR FROM a.activity_month) - EXTRACT(YEAR FROM c.cohort_month)) * 12
      + (EXTRACT(MONTH FROM a.activity_month) - EXTRACT(MONTH FROM c.cohort_month)))::int
      AS month_number
  FROM cohorts AS c
  JOIN activity AS a ON a.user_id = c.user_id
  WHERE a.activity_month >= c.cohort_month
)
SELECT
  ca.cohort_month,
  s.users AS cohort_size,
  ca.month_number,
  COUNT(DISTINCT ca.user_id) AS active_users,
  ROUND(100.0 * COUNT(DISTINCT ca.user_id) / s.users, 1) AS retention_pct
FROM cohort_activity AS ca
JOIN cohort_size AS s ON s.cohort_month = ca.cohort_month
GROUP BY ca.cohort_month, s.users, ca.month_number
ORDER BY ca.cohort_month, ca.month_number;

Cohort size comes from the cohorts table, not from the join, so users who were never active still count in the denominator. Computing the denominator from joined rows is one of the most common retention bugs, and it inflates every number. The joins themselves are covered in the SQL joins guide.

Pivot the result into a matrix

The query above returns a long table, which is ideal for BI tools. To see the classic matrix directly in SQL, pivot with PostgreSQL's FILTER clause by replacing the final SELECT:

SELECT
  ca.cohort_month,
  s.users AS cohort_size,
  ROUND(100.0 * COUNT(DISTINCT ca.user_id) FILTER (WHERE ca.month_number = 1) / s.users, 1) AS m1,
  ROUND(100.0 * COUNT(DISTINCT ca.user_id) FILTER (WHERE ca.month_number = 2) / s.users, 1) AS m2,
  ROUND(100.0 * COUNT(DISTINCT ca.user_id) FILTER (WHERE ca.month_number = 3) / s.users, 1) AS m3
FROM cohort_activity AS ca
JOIN cohort_size AS s ON s.cohort_month = ca.cohort_month
GROUP BY ca.cohort_month, s.users
ORDER BY ca.cohort_month;

Month 0 is the signup month itself. For activity-based cohorts it is always 100%, so many teams leave it out.

Step 4: How to read a retention triangle

The output forms a triangle, because recent cohorts have not been around long enough to have later months. For example, an illustrative matrix might look like this:

Read it three ways:

1. Across a row: how one cohort decays over time. A curve that flattens means you have a core of retained users; one that keeps falling toward zero means you do not.
2. Down a column: whether newer cohorts retain better at the same age. In the example, M1 improves from 38% to 45%, which suggests onboarding changes are working.
3. Along a diagonal: cells in the same calendar month. A dip across a diagonal points to a calendar event, such as an outage or seasonality, rather than a cohort effect.

Small cohorts produce noisy percentages, so show cohort size next to every row and be cautious about differences of a few points.

Handle incomplete periods

The most recent cell in each row may cover a period that has not finished yet. Exclude periods that are still in progress, otherwise the last column always looks like a drop. In the query, add a condition such as a.activity_month < date_trunc('month', now())::date.

N-day vs unbounded vs bracket retention

Monthly cohorts are only one choice. Daily retention definitions answer different questions, and mixing them up leads to confusing comparisons.

Unbounded retention is always greater than or equal to N-day retention for the same N, since it counts more activity. It also changes after the fact, because a user who returns later retroactively counts as retained on earlier days.

Here is classic day-7 retention by signup date. In PostgreSQL, subtracting two date values gives an integer number of days:

WITH first_seen AS (
  SELECT user_id, signup_at::date AS start_date
  FROM users
  WHERE signup_at::date <= CURRENT_DATE - 8
),
day7 AS (
  SELECT DISTINCT e.user_id
  FROM events AS e
  JOIN first_seen AS f ON f.user_id = e.user_id
  WHERE e.event_at::date - f.start_date = 7
)
SELECT
  f.start_date,
  COUNT(*) AS cohort_size,
  ROUND(100.0 * COUNT(d.user_id) / COUNT(*), 1) AS day7_retention_pct
FROM first_seen AS f
LEFT JOIN day7 AS d ON d.user_id = f.user_id
GROUP BY f.start_date
ORDER BY f.start_date;

The filter on CURRENT_DATE - 8 keeps only users whose day 7 is fully in the past. Change the day condition to BETWEEN 7 AND 13 for bracket retention, or to >= 7 for unbounded retention. On large event tables, an index on (user_id, event_at) helps these joins; see the database indexes guide.

Key takeaways

• Cohort retention compares groups of users at the same age, which blended active-user counts cannot do.
• Define the cohort event and a meaningful activity event before writing any SQL.
• Take cohort size from the full cohort, not from joined activity rows, or retention will be inflated.
• Read the triangle across rows for decay, down columns for improvement, and along diagonals for calendar effects.
• Choose N-day, unbounded, or bracket retention to match how often users are expected to return, and exclude incomplete periods.

Frequently asked questions

What is a good retention rate?

It depends heavily on the product category, the activity definition, and the time period, so there is no universal benchmark. The most useful comparison is against your own earlier cohorts. A retention curve that flattens rather than trending to zero is a stronger signal than any single percentage.

Should cohorts be based on signup or first purchase?

Use the event that marks the start of the relationship you care about. Signup cohorts suit engagement questions, while first-purchase cohorts suit revenue and repeat-purchase questions. Many teams maintain both and label them clearly.

Why does my latest cohort show much lower retention?

Usually because its later periods are incomplete. A cohort that started this month has not had a full month 1 yet. Filter out periods that are still in progress, and only compare cohorts at ages they have all fully reached.

What is the difference between retention and churn?

Retention is the share of a cohort still active in a period; churn is the share that stopped. For a single step they are complements, but churn is often defined over a rolling window, such as no activity for 30 days, so check definitions before converting one into the other.