Retention Cohort Analysis in SQL (Step-by-Step With Query)

What a cohort retention analysis actually answers

A single retention number — "we retain 40% of users" — hides everything useful. It doesn't say retained from when, or whether retention is getting better or worse for newer users. Cohort analysis fixes that by splitting users into groups based on when they joined, then tracking each group separately over time. That's what turns "40% retention" into "cohorts from March are retaining worse than cohorts from January — something changed in February."

The output is a grid: rows are cohorts (signup month), columns are periods since signup (Month 0, Month 1, Month 2…), and each cell is the percentage of that cohort still active in that period. Reading down a column tells you if retention is improving cohort-over-cohort. Reading across a row tells you how a single cohort decays over time.

The two things you need: a cohort date and an activity date

Every cohort analysis needs exactly two date columns, whether they live in one table or two: a cohort-defining event (almost always signup) and a stream of activity events (logins, orders, sessions — whatever "retained" means for your product). Here's a minimal two-table setup used throughout this article:

Step 1 — Assign each user to a cohort

Truncate signup_at down to a month (or week) so every user who joined in the same period shares the exact same cohort value:

SELECT
  user_id,
  DATE_TRUNC('month', signup_at) AS cohort_month
FROM users;

Two users who sign up on March 3rd and March 29th both get cohort_month = 2026-03-01. This is the anchor everything else measures against, and it's why raw timestamps are useless here — no two signups happen at the exact same second, so nothing would ever group.

Step 2 — Calculate the period offset

For every activity event, work out how many periods have passed since that user's cohort month. This is the join that connects "what a user did" to "how long after they joined they did it":

WITH cohorts AS (
  SELECT user_id, DATE_TRUNC('month', signup_at) AS cohort_month
  FROM users
)
SELECT
  c.user_id,
  c.cohort_month,
  DATE_TRUNC('month', e.event_at) AS activity_month,
  DATEDIFF('month', c.cohort_month, DATE_TRUNC('month', e.event_at)) AS period_number
FROM cohorts c
JOIN events e ON e.user_id = c.user_id;

period_number = 0 is the cohort's signup month itself (they're trivially "active" — they just joined). period_number = 1 means active exactly one month after joining, and so on. DATEDIFF's exact syntax varies by database — see the dialect notes below.

Step 3 — Aggregate into the cohort grid

Count distinct active users per cohort, per period. DISTINCT matters here — a user with ten events in the same month should count once, not ten times:

SELECT
  cohort_month,
  period_number,
  COUNT(DISTINCT user_id) AS active_users
FROM offsets
GROUP BY cohort_month, period_number
ORDER BY cohort_month, period_number;

The full query, start to finish

Chained together as CTEs, the whole analysis is one query — no procedural loop, no app-layer post-processing:

WITH cohorts AS (
  SELECT user_id, DATE_TRUNC('month', signup_at) AS cohort_month
  FROM users
),

offsets AS (
  SELECT
    c.user_id,
    c.cohort_month,
    DATEDIFF('month', c.cohort_month, DATE_TRUNC('month', e.event_at)) AS period_number
  FROM cohorts c
  JOIN events e ON e.user_id = c.user_id
  WHERE e.event_at >= c.cohort_month
),

cohort_size AS (
  SELECT cohort_month, COUNT(DISTINCT user_id) AS total_users
  FROM cohorts
  GROUP BY cohort_month
),

active_by_period AS (
  SELECT cohort_month, period_number, COUNT(DISTINCT user_id) AS active_users
  FROM offsets
  GROUP BY cohort_month, period_number
)

SELECT
  a.cohort_month,
  a.period_number,
  a.active_users,
  s.total_users,
  ROUND(a.active_users * 100.0 / s.total_users, 1) AS retention_pct
FROM active_by_period a
JOIN cohort_size s ON s.cohort_month = a.cohort_month
ORDER BY a.cohort_month, a.period_number;

Turning raw counts into retention percentages

The query above joins back to a separate cohort_size CTE to get period-0 totals, which is the clearest approach to read and debug. If you'd rather avoid the extra join, a window function does it inline:

SELECT
  cohort_month,
  period_number,
  active_users,
  ROUND(
    active_users * 100.0 /
    FIRST_VALUE(active_users) OVER (
      PARTITION BY cohort_month ORDER BY period_number
    ),
  1) AS retention_pct
FROM active_by_period;

This works because period 0 is, by construction, always the first row per cohort when ordered by period_number — so FIRST_VALUE always returns the cohort's total size. If you're not yet comfortable with window function partitioning, PARTITION BY Explained in SQL covers exactly this pattern.

Pivoting rows into the triangle shape

The query above returns one row per cohort-period pair — correct, but not the "triangle" shape people expect from a cohort chart. Getting there is a pivot, done with conditional aggregation so it works identically across databases without a dialect-specific PIVOT operator:

SELECT
  cohort_month,
  MAX(CASE WHEN period_number = 0 THEN retention_pct END) AS m0,
  MAX(CASE WHEN period_number = 1 THEN retention_pct END) AS m1,
  MAX(CASE WHEN period_number = 2 THEN retention_pct END) AS m2,
  MAX(CASE WHEN period_number = 3 THEN retention_pct END) AS m3
FROM cohort_retention
GROUP BY cohort_month
ORDER BY cohort_month;

Each MAX(CASE WHEN ...) pair picks out one period and spreads it into its own column — the same conditional-aggregation trick used to hand-roll a pivot table. Most BI tools (and spreadsheet exports) can also do this last step for you, so it's common to stop at the row-per-period version and let the visualization layer handle the pivot.

Weekly vs. monthly cohorts

The query structure is identical for both — only the DATE_TRUNC grain and the DATEDIFF unit change. It's common to build both and use monthly for exec reporting, weekly for product-team debugging of a specific onboarding change.

Common mistakes

• Counting events, not distinct users. Forgetting DISTINCT in COUNT(DISTINCT user_id) means a single power user's activity inflates the whole cohort's retention rate.
• Using raw timestamps as the cohort key. Without DATE_TRUNC, every user becomes their own cohort of one, and every retention rate is meaningless (0% or 100%).
• Including events before the cohort date. Backdated test data or timezone mismatches between signup_at and event_at can produce negative period numbers that silently skew the earliest column.
• Comparing an incomplete cohort's later periods. A cohort that signed up last month has no "period 3" data yet — showing it as 0% retention is misleading; it should be blank or excluded until enough time has passed.
• Mixing cohort granularity mid-report. Truncating some cohorts by week and others by month in the same table produces a grid that can't be compared row to row.