← All cheatsheets
Data Analyst · #093 · November 5, 2026 · 3 min read

Cohort retention in SQL: the triangle table and the tail that goes up

Signup month by months since signup, the query that builds the table, why month 0 must be 100%, the triangle you should expect, and why the blended retention curve bends up when only the oldest cohorts are left: one page.

Get the free PDF

One page, print-ready, free to share. No signup needed.

Download the PDF

Quarterly review, slide 14: month 10 retention went up. Nobody came back. The curve is an average over whoever has reached month 10, and by then that is three cohorts of early adopters. This page covers the cohort table (signup month by months since signup), the query that builds it, the month 0 check, the triangle, and why the blended tail bends up. The print-ready A4 PDF is at the bottom.

The table

  • Cohort: the month of each user's first event.
  • month_n: months since that: 0, 1, 2 and so on.
  • Month 0 must be 100%. If it is not, the join is wrong.

The query

  • MIN(ts) per user: one row per user, the cohort.
  • Join the activity: months between the activity month and the cohort month.
  • COUNT(DISTINCT user_id): users per cell, not events.

The cohort table, one query

One row per user for the cohort, one row per user and month for the activity, a join, a count. Postgres.

WITH cohorts AS (
  SELECT user_id,
         MIN(DATE_TRUNC('month', ts)) AS cohort
  FROM events GROUP BY user_id
), activity AS (
  SELECT DISTINCT user_id,
         DATE_TRUNC('month', ts) AS month
  FROM events
)
SELECT c.cohort,
       EXTRACT(YEAR FROM AGE(a.month, c.cohort)) * 12
     + EXTRACT(MONTH FROM AGE(a.month, c.cohort))
       AS month_n,
       COUNT(DISTINCT a.user_id) AS users
FROM cohorts c JOIN activity a USING (user_id)
GROUP BY 1, 2 ORDER BY 1, 2;

To turn the counts into percentages, divide each cell by its cohort's month 0:

SELECT cohort, month_n, users,
       ROUND(100.0 * users / FIRST_VALUE(users) OVER (
         PARTITION BY cohort ORDER BY month_n), 1) AS pct
FROM cohort_counts;

where cohort_counts is the first query. In BigQuery, DATE_DIFF(a.month, c.cohort, MONTH) replaces the two EXTRACT lines.

Remember

  • The triangle: a cohort from three months ago has no month 6 yet. Empty cells on the right are expected.
  • The curve going up: only old cohorts reach month 10.

The trap: three ways the table lies

You seeCauseFix
month 0 = 83%cohort from signups, activity from events: 17% never fired an eventboth from the same table, or call it activation
month 1 = 140%COUNT(*) on events, not usersCOUNT(DISTINCT user_id)
month 10 jumps uponly the 2025 cohorts are 10 months oldshow n per cell, cut the tail

The blended curve averages over whoever has reached that age. At month 10 that is the early adopters only. The curve did not improve, the mix did.

The other way a tail goes up is survivorship in the data itself: churned accounts get deleted, the users table shrinks, and a query that takes its denominator from the users table divides by fewer people every month. Keep the cohort size from the first query, computed once, and never from a table that loses rows.

The quiz

Which one gives month 0 = 100%?

A) SELECT DATE_TRUNC('month', created_at)
     AS cohort, id AS user_id
   FROM users;

B) SELECT MIN(DATE_TRUNC('month', ts))
     AS cohort, user_id
   FROM events
   GROUP BY user_id;

B. In B the cohort is the month of each user's first event, so every user is active in their own month 0 by construction: 100%. In A the cohort comes from the signup date; users who signed up and never fired an event sit in the denominator and not in the numerator, so month 0 lands at 80 to 90%. A is not wrong, it measures activation. It is just not the table with 100% on the left.

The window function in the percentage query is on the window functions sheet.

Frequently asked questions

What is a cohort retention table?
One row per signup month, one column per month since signup. Each cell is the share of that cohort still active that many months later. Month 0 is 100% by construction, the columns to the right go down, and young cohorts have fewer filled columns, which gives the table its triangle shape.
How do I compute it in SQL?
Two CTEs and a join. One row per user with the month of their first event (the cohort). One row per user and month with activity. Join on user_id, compute the months between the activity month and the cohort month, count distinct users per cohort and month number.
Why is my month 0 not 100%?
Usually because the cohort comes from one table (signups) and the activity from another (events). Users who signed up and never fired an event are in the denominator and not in the numerator. That is a useful activation number, but it is not the retention table with 100% on the left.
Why does the overall retention curve go up at month 10?
Because only the oldest cohorts have reached month 10. The blended curve averages over whoever is that old, and at the tail that is the early adopters only. The curve did not improve, the mix of cohorts in it changed. Show n per cell, or cut the tail where fewer than a few cohorts remain.

Get the free PDF

One page, print-ready, free to share. No signup needed.

Download the PDF

More cheatsheets