How to Calculate Cohort Retention in SQL

Most retention numbers I see are wrong. Not obviously wrong — they look reasonable, they go in the board deck, nobody questions them. But they're computed in a way that silently inflates the percentages. The usual mistake is an INNER JOIN between users and activity that drops churned users entirely, which means your "Month 3 retention" only includes people who were still around in Month 3. That's not retention. That's a tautology.

The correct approach is a three-step build: assign each user to a cohort, construct an activity matrix with LEFT JOIN so that zeros show up, then pivot into percentages. I'll show all three in Postgres first, then a DuckDB variant for cross-source analysis.

Step 1: Cohort assignment

Every retention analysis starts with one question — when did each user "start"? Usually that's signup. Could be first purchase, first login, whatever your product considers the starting gun.

SELECT
  user_id,
  DATE_TRUNC('month', MIN(created_at)) AS cohort_month
FROM events
WHERE event_name = 'signup_created'
GROUP BY user_id

MIN(created_at) matters. If a user has duplicate signup events (happens more often than you'd think — webhook retries, double-click on the signup button, re-registration after a failed onboarding), you want the earliest one. Without MIN, a single user gets scattered across multiple cohorts, and your retention matrix grows phantom users that inflate early cohorts and dilute later ones.

For retention by first purchase instead of signup:

SELECT
  user_id,
  DATE_TRUNC('month', MIN(created_at)) AS cohort_month
FROM orders
WHERE status = 'completed'
GROUP BY user_id

Same shape, different event. The rest of the pipeline doesn't change.

Step 2: The activity matrix

Now you need to know which months each user was active in. "Active" requires a real definition — not just "has a row somewhere." Pick events that signal actual engagement: logins, queries run, features used, purchases made.

WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(created_at)) AS cohort_month
  FROM events
  WHERE event_name = 'signup_created'
  GROUP BY user_id
),
monthly_activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', created_at) AS active_month
  FROM events
  WHERE event_name IN ('login', 'query_created', 'dashboard_viewed', 'report_exported')
)
SELECT
  c.cohort_month,
  c.user_id,
  a.active_month,
  EXTRACT(YEAR FROM AGE(a.active_month, c.cohort_month)) * 12
    + EXTRACT(MONTH FROM AGE(a.active_month, c.cohort_month)) AS month_number
FROM cohorts c
LEFT JOIN monthly_activity a
  ON a.user_id = c.user_id
  AND a.active_month >= c.cohort_month

Two things make this correct rather than just plausible-looking.

First, the LEFT JOIN. An INNER JOIN silently drops every user who never came back. Those users vanish from your cohort size, and your retention percentages go up for the wrong reason. With LEFT JOIN, churned users appear with NULL in active_month and month_number — they count toward the denominator but not the numerator.

Second, the month_number calculation uses AGE() instead of epoch arithmetic. I've seen EXTRACT(EPOCH FROM ...) / (30 * 86400) in plenty of blog posts and it's wrong in production. Months aren't 30 days long. A user active on February 28th and March 1st, one day apart, gets assigned to two different month buckets with the epoch approach. AGE() counts calendar months, which is what you actually want.

Step 3: The full retention table

Aggregate the activity matrix into the classic triangle — one row per cohort, one column per period:

WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(created_at)) AS cohort_month
  FROM events
  WHERE event_name = 'signup_created'
  GROUP BY user_id
),
monthly_activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', created_at) AS active_month
  FROM events
  WHERE event_name IN ('login', 'query_created', 'dashboard_viewed', 'report_exported')
),
retention_data AS (
  SELECT
    c.cohort_month,
    c.user_id,
    EXTRACT(YEAR FROM AGE(a.active_month, c.cohort_month)) * 12
      + EXTRACT(MONTH FROM AGE(a.active_month, c.cohort_month)) AS month_number
  FROM cohorts c
  LEFT JOIN monthly_activity a
    ON a.user_id = c.user_id
    AND a.active_month >= c.cohort_month
)
SELECT
  cohort_month,
  COUNT(DISTINCT user_id) FILTER (WHERE month_number = 0) AS month_0,
  COUNT(DISTINCT user_id) FILTER (WHERE month_number = 1) AS month_1,
  COUNT(DISTINCT user_id) FILTER (WHERE month_number = 2) AS month_2,
  COUNT(DISTINCT user_id) FILTER (WHERE month_number = 3) AS month_3,
  COUNT(DISTINCT user_id) FILTER (WHERE month_number = 4) AS month_4,
  COUNT(DISTINCT user_id) FILTER (WHERE month_number = 5) AS month_5,
  ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE month_number = 1)
    / NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE month_number = 0), 0), 1) AS m1_pct,
  ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE month_number = 2)
    / NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE month_number = 0), 0), 1) AS m2_pct,
  ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE month_number = 3)
    / NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE month_number = 0), 0), 1) AS m3_pct
FROM retention_data
GROUP BY cohort_month
ORDER BY cohort_month

The percentages divide by month_0, not total cohort size. That's intentional — month_0 counts users who were actually active in their signup month (performed a qualifying event), which is your real baseline. A user who signed up and never logged in shouldn't inflate the denominator if you're measuring usage retention versus existence retention.

One trap: the trailing edge of the triangle. Your January cohort has data through month 6, but the July cohort only has month 0. The raw zeros in the bottom-right corner aren't "0% retention" — they're "the month hasn't happened yet." Either filter with WHERE cohort_month <= NOW() - INTERVAL '3 months' to only show complete rows, or use generate_series to build out the time axis and mark incomplete periods as NULL instead of 0.

Weekly cohorts

Monthly cohorts smooth over too much. If you ship weekly and want to see whether last Tuesday's release moved retention, switch the grain:

WITH cohorts AS (
  SELECT user_id, DATE_TRUNC('week', MIN(created_at)) AS cohort_week
  FROM events WHERE event_name = 'signup_created'
  GROUP BY user_id
),
weekly_activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('week', created_at) AS active_week
  FROM events
  WHERE event_name IN ('login', 'query_created', 'dashboard_viewed')
),
retention_data AS (
  SELECT
    c.cohort_week,
    c.user_id,
    (a.active_week - c.cohort_week) / 7 AS week_number
  FROM cohorts c
  LEFT JOIN weekly_activity a
    ON a.user_id = c.user_id
    AND a.active_week >= c.cohort_week
)
SELECT
  cohort_week,
  COUNT(DISTINCT user_id) FILTER (WHERE week_number = 0) AS w0,
  COUNT(DISTINCT user_id) FILTER (WHERE week_number = 1) AS w1,
  COUNT(DISTINCT user_id) FILTER (WHERE week_number = 2) AS w2,
  COUNT(DISTINCT user_id) FILTER (WHERE week_number = 3) AS w3,
  COUNT(DISTINCT user_id) FILTER (WHERE week_number = 4) AS w4,
  ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE week_number = 1)
    / NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE week_number = 0), 0), 1) AS w1_pct,
  ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE week_number = 2)
    / NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE week_number = 0), 0), 1) AS w2_pct
FROM retention_data
GROUP BY cohort_week
ORDER BY cohort_week

The week_number math is simpler — DATE_TRUNC('week', ...) in Postgres returns dates, and subtracting two dates gives you days as an integer. Divide by 7 and you're done. No AGE() needed because weeks are always seven days, unlike months.

Weekly cohorts produce wide tables fast. Twelve weeks is twelve columns. You'll probably want to limit the lookback window or transpose the output for display.

DuckDB variant

If you're pulling data from multiple sources into DuckDB for cross-source joins, the retention query is nearly identical. The one real difference is month diffing:

WITH cohorts AS (
  SELECT user_id, DATE_TRUNC('month', MIN(created_at)) AS cohort_month
  FROM events WHERE event_name = 'signup_created'
  GROUP BY user_id
),
monthly_activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', created_at) AS active_month
  FROM events
  WHERE event_name IN ('login', 'query_created', 'dashboard_viewed', 'report_exported')
),
retention_data AS (
  SELECT
    c.cohort_month,
    c.user_id,
    DATEDIFF('month', c.cohort_month, a.active_month) AS month_number
  FROM cohorts c
  LEFT JOIN monthly_activity a
    ON a.user_id = c.user_id
    AND a.active_month >= c.cohort_month
)
SELECT
  cohort_month,
  COUNT(DISTINCT user_id) FILTER (WHERE month_number = 0) AS month_0,
  COUNT(DISTINCT user_id) FILTER (WHERE month_number = 1) AS month_1,
  COUNT(DISTINCT user_id) FILTER (WHERE month_number = 2) AS month_2,
  COUNT(DISTINCT user_id) FILTER (WHERE month_number = 3) AS month_3,
  ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE month_number = 1)
    / NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE month_number = 0), 0), 1) AS m1_pct,
  ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE month_number = 2)
    / NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE month_number = 0), 0), 1) AS m2_pct,
  ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE month_number = 3)
    / NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE month_number = 0), 0), 1) AS m3_pct
FROM retention_data
GROUP BY cohort_month
ORDER BY cohort_month

DATEDIFF('month', start, end) replaces the AGE() + EXTRACT() chain from Postgres. It counts calendar month boundaries between two dates — cleaner to write and harder to get wrong. DuckDB also supports FILTER (WHERE ...), so the aggregation logic is identical.

The real power of running this in DuckDB through Fastero is combining sources. You can join events from your product database with stripe_subscriptions pulled from Stripe, and suddenly your cohort definition can factor in payment behavior alongside usage. Want retention by pricing tier? By acquisition channel from HubSpot? By first-purchase amount? Those are just additional JOIN clauses against data that's already in your cross-source store.

Pitfalls that silently wreck your numbers

Timezone handling. If events.created_at stores UTC timestamps (it should), a user who signed up at 11pm Eastern on January 31st is recorded as February 1st UTC and lands in the wrong cohort. Fix it before truncating:

DATE_TRUNC('month', created_at AT TIME ZONE 'America/New_York')

Same syntax in both Postgres and DuckDB. But watch out: if your timestamps are naive TIMESTAMP (no timezone info) rather than TIMESTAMPTZ, the AT TIME ZONE operator does the opposite of what you'd expect — it interprets the value as being in that timezone and converts to UTC, rather than converting from UTC. Check your column types first.

"Active" vs "paid" retention. These are fundamentally different metrics. A user who logs in daily but downgraded to the free plan is "retained" in activity retention and "churned" in revenue retention. Mixing them in one table hides the thing you're trying to measure. Build two separate retention tables and label them clearly. If you're tracking revenue retention specifically, you probably want net revenue retention instead of user-count retention anyway.

Off-by-one in month diffing. Don't divide epoch seconds by 30 * 86400 to get months. Use AGE() in Postgres, DATEDIFF in DuckDB. The epoch approach assigns users to the wrong month bucket whenever a month isn't exactly 30 days, and the error compounds across longer retention windows.

Incomplete trailing cohorts. Your most recent cohort will always show zeros for later periods that haven't elapsed yet. Those zeros aren't retention data, they're missing data. Filter your output, or use generate_series(MIN(cohort_month), MAX(cohort_month), INTERVAL '1 month') to build a complete time spine and mark future periods as NULL instead. A zero that means "nobody came back" and a zero that means "the month hasn't happened" look identical in a table, and only one of them should concern you.

From query to dashboard widget

Running cohort queries manually works for one-off analysis. For something you want to track week over week, you need a dashboard that updates itself. In Fastero, paste the full retention query into the SQL editor, run it, and add the result as a table widget on a dashboard. Set it to refresh daily or weekly.

Pair it with a number widget showing current Month 1 retention as a single KPI, and set up a Slack alert that fires if that number drops below your baseline. Three widgets and one alert — maybe 20 minutes of setup — and your retention reporting runs itself from that point on.

Try Fastero free — connect your database, build retention cohorts in SQL, and turn them into live dashboards that refresh on a schedule. No credit card required.