Your signup chart is going up and to the right. Revenue is growing. Everyone's happy. And yet something feels off — support tickets keep repeating, the same features get "discovered" by the same users every month, and nobody can quite say whether the people who joined in January are still around in June.
That gnawing feeling has a name, and it has a query. Cohort retention analysis is the single most honest report you can run against your database. It takes your users, groups them by when they joined, and tracks how many of each group keep coming back over time. A growing signup number can hide a leaky bucket. A retention cohort table cannot — it shows you, month by month, exactly how fast your bucket leaks.
In this article we'll build a full cohort retention query from scratch using plain SQL, look at the difference between the three kinds of retention people confuse constantly, and cover the one mistake that makes almost every hand-rolled retention report wrong. By the end you'll have a query you can point at your own users and events tables today.
The two tables you need
You don't need an analytics warehouse for this. Two tables most SaaS apps already have will do:
| Table | Columns we care about |
|---|---|
users |
id, created_at
|
events |
user_id, occurred_at, event_name
|
The events table is whatever signals "this user is getting value" — a login, a report run, a message sent, an API call. Pick the action that means someone is actually using the product, not just a passive ping. This choice matters more than any SQL trick below: if you count "opened a marketing email" as activity, your retention will look great and mean nothing.
Step 1: Define the cohort
A cohort is just a group of users bucketed by the period they joined. We'll use signup month. In PostgreSQL, date_trunc snaps a timestamp down to the start of a period, which makes the bucketing trivial:
-- Each user's cohort = the month they signed up
SELECT
id AS user_id,
date_trunc('month', created_at)::date AS cohort_month
FROM users;
That gives every user a cohort_month like 2026-01-01. Everyone who signed up in January shares the same anchor, and that anchor never changes for them. This CTE is the foundation of everything else — and, importantly, the source of your denominator. Hold that thought.
Step 2: Find each user's activity months
Next we bucket every activity event into the month it happened:
SELECT DISTINCT
user_id,
date_trunc('month', occurred_at)::date AS activity_month
FROM events;
DISTINCT matters here. A power user might fire 400 events in March, but for retention we only care whether they were active that month, not how many times. Skip the DISTINCT and your later counts will double-count active users and blow past 100% retention — a classic "that can't be right" moment.
Step 3: Join them and measure the gap
Now we connect each cohort to its activity and compute how many months after signup each activity happened. This is where the shape of the report appears:
WITH cohort AS (
SELECT id AS user_id,
date_trunc('month', created_at)::date AS cohort_month
FROM users
),
activity AS (
SELECT DISTINCT user_id,
date_trunc('month', occurred_at)::date AS activity_month
FROM events
)
SELECT
c.cohort_month,
-- whole months between signup and the activity
(EXTRACT(YEAR FROM age(a.activity_month, c.cohort_month)) * 12
+ EXTRACT(MONTH FROM age(a.activity_month, c.cohort_month)))::int AS month_number,
COUNT(DISTINCT a.user_id) AS active_users
FROM cohort c
LEFT JOIN activity a
ON a.user_id = c.user_id
AND a.activity_month >= c.cohort_month
GROUP BY 1, 2
ORDER BY 1, 2;
month_number = 0 is the signup month, 1 is the month after, and so on. The result is a long, tidy table: one row per (cohort, month offset) with a count of how many users were active.
Step 4: Turn it into the triangle
Retention tables are usually shown as a triangle — cohorts down the side, month offsets across the top. FILTER (or CASE) pivots the long output into columns:
WITH cohort AS (
SELECT id AS user_id,
date_trunc('month', created_at)::date AS cohort_month
FROM users
),
activity AS (
SELECT DISTINCT user_id,
date_trunc('month', occurred_at)::date AS activity_month
FROM events
),
joined AS (
SELECT c.cohort_month, c.user_id,
(EXTRACT(YEAR FROM age(a.activity_month, c.cohort_month)) * 12
+ EXTRACT(MONTH FROM age(a.activity_month, c.cohort_month)))::int AS month_number
FROM cohort c
LEFT JOIN activity a
ON a.user_id = c.user_id
AND a.activity_month >= c.cohort_month
)
SELECT
cohort_month,
COUNT(DISTINCT user_id) AS cohort_size,
COUNT(DISTINCT user_id) FILTER (WHERE month_number = 1) AS m1,
COUNT(DISTINCT user_id) FILTER (WHERE month_number = 2) AS m2,
COUNT(DISTINCT user_id) FILTER (WHERE month_number = 3) AS m3
FROM joined
GROUP BY cohort_month
ORDER BY cohort_month;
Divide each mN by cohort_size and you get retention percentages. A real result looks like this:
| Cohort | Size | Month 1 | Month 2 | Month 3 |
|---|---|---|---|---|
| 2026-01 | 820 | 44% | 31% | 27% |
| 2026-02 | 910 | 47% | 34% | — |
| 2026-03 | 1,050 | 52% | — | — |
Read it two ways. Down a column tells you whether newer cohorts retain better than older ones — here Month 1 climbs from 44% to 52%, so something you shipped is working. Across a row tells you the shape of the decay: a curve that flattens (27% → 27% → 27%) means you've found a loyal core; a curve that keeps sliding toward zero means you have no floor, and that's an existential problem.
Bounded vs. rolling vs. classic retention
People say "retention" and mean three different things. Getting this wrong makes two teams argue about numbers that were never measuring the same thing.
| Type | Question it answers | SQL condition |
|---|---|---|
| Classic (period) retention | Were they active in month N? | month_number = N |
| Bounded / Day-N retention | Were they active on exactly day N? | activity_date = cohort_date + N |
| Rolling retention | Were they active on or after month N? | month_number >= N |
The query above is classic period retention, which is the right default for most SaaS products. Bounded (=) is stricter and used for habit-forming apps where daily use is the goal. Rolling (>=) is the most forgiving — it counts a user as retained at Month 3 if they showed up any time from Month 3 onward, which is useful for infrequent-but-valuable products like a tax tool or an annual-review app. There's no universally correct one; there's only the one that matches how your product is meant to be used. Just be sure everyone in the room is looking at the same definition.
The mistakes that quietly ruin retention reports
The denominator bug (the big one). This is the mistake in the majority of hand-written retention queries, and it always inflates your numbers. If you use INNER JOIN instead of LEFT JOIN, or add a WHERE clause on the activity, users who never came back silently disappear from the cohort entirely. Now you're computing "of the users who returned, how many returned?" — which is close to 100% by construction. The denominator must always be the full cohort, including the people who churned. Keep the LEFT JOIN, and never filter the cohort CTE by activity.
Sanity check it independently:
-- cohort_size here must equal the size in your report
SELECT date_trunc('month', created_at)::date AS cohort_month,
COUNT(DISTINCT id) AS cohort_size
FROM users
GROUP BY 1 ORDER BY 1;
If those numbers don't match your report's cohort_size, your join is dropping churned users.
Partial cohorts. This month's cohort hasn't lived a full month yet, so its later columns look artificially low. Don't panic over a 12% Month-1 for the current cohort — it's incomplete, not collapsing. Either exclude cohorts that haven't matured or grey them out in the dashboard.
Timezone and week boundaries. date_trunc uses whatever timezone your timestamps are stored in. If created_at is UTC but your users are in California, a signup at 9pm Pacific on Jan 31 lands in February's cohort. Normalize to a single timezone (created_at AT TIME ZONE 'America/Los_Angeles') before truncating, and pick one week-start convention if you cohort by week.
Choosing a vanity activity event. Worth repeating: if your "activity" is anything a user can trigger without engaging (a background sync, an email open), your retention curve is measuring your cron jobs, not your product.
Key takeaways
Cohort retention is the report that can't be faked by a good growth month. Group users by signup period, count distinct active users per later period, and always divide by the full cohort — churned users included. Use LEFT JOIN, DISTINCT your activity, exclude immature cohorts, and agree on whether you mean classic, bounded, or rolling retention before anyone reads a number off the chart. Get those right and you'll have a table that tells you the truth about whether people actually stick around.
The query itself is 30 lines. The hard part is running it regularly and putting it somewhere your whole team sees it — which is exactly the point where a raw SQL query needs to become a living dashboard. If you're already piping these cohorts into a chart or embedding them in your product, I'd love to hear how you've set it up.
How do you define an "active" user for your retention math — and has changing that definition ever completely rewritten your curve? Drop your approach in the comments.
Top comments (1)
The denominator and maturity warnings are the ones that save real dashboards. One further safeguard is to version the metric contract itself: qualifying event names, identity stitching rules, bot/internal-user exclusions, timezone, grace window, and query revision. Otherwise a cleaner event taxonomy can look like a retention improvement. I also like emitting an
observed_throughdate and a booleanis_maturefor every cohort-period cell rather than relying on dashboard shading. For products with account-level buying and multiple users, run the same matrix at both user and organization grain; seat churn and logo retention answer different questions. Finally, late-arriving events need an explicit policy—recompute open cohorts through a watermark, then freeze or annotate older cells—so historical percentages do not silently move.