Cohort and retention queries

Why it matters

Total active users can rise every month while the product is quietly failing: if marketing brings in lots of new people who all leave after a week, the headline number hides it. Cohort analysis groups users by when they started (their cohort) and tracks what share are still active one, two, three months later. The resulting retention table is one of the most requested analyses at subscription, app and e-commerce companies, and it is a frequent interview question for analyst roles.

The shape of a retention table

Read across a row to see how one cohort decays. Read down a column to see whether newer cohorts retain better than older ones (did last month's onboarding change help?). The triangle shape is normal: newer cohorts have not had time to reach month 2 yet.

In SQL it is easier to produce the long form, one row per cohort and month number, and let a spreadsheet or BI tool pivot it:

Step 1: assign each user to a cohort

Usually the cohort is the month of the user's first event: signup, first purchase or first login. If it is not stored, derive it:

SELECT user_id, MIN(substr(order_date, 1, 7)) AS cohort_month
FROM orders
GROUP BY user_id;

substr(date, 1, 7) turns '2026-01-15' into '2026-01'. (PostgreSQL would use date_trunc('month', order_date).)

Step 2: measure months since the cohort

To subtract months, turn YYYY-MM into a single month count: year × 12 + month.

(CAST(substr(activity_month, 1, 4) AS INTEGER) * 12 + CAST(substr(activity_month, 6, 2) AS INTEGER))
- (CAST(substr(signup_month, 1, 4) AS INTEGER) * 12 + CAST(substr(signup_month, 6, 2) AS INTEGER))

January 2026 to March 2026 gives (2026*12+3) - (2026*12+1) = 2, and the formula works across year boundaries (December to January is 1). This month_number is the column header of the retention table.

Step 3: count distinct users, divide by cohort size

Put it together with CTEs:

WITH cohort_size AS (
  SELECT signup_month, COUNT(*) AS users
  FROM users
  GROUP BY signup_month
),
user_months AS (
  SELECT DISTINCT u.id AS user_id, u.signup_month,
         (CAST(substr(a.activity_month, 1, 4) AS INTEGER) * 12 + CAST(substr(a.activity_month, 6, 2) AS INTEGER))
       - (CAST(substr(u.signup_month, 1, 4) AS INTEGER) * 12 + CAST(substr(u.signup_month, 6, 2) AS INTEGER)) AS month_number
  FROM users AS u
  JOIN activity AS a ON a.user_id = u.id
)
SELECT m.signup_month AS cohort_month,
       m.month_number,
       COUNT(*) AS active_users,
       ROUND(COUNT(*) * 100.0 / s.users, 1) AS retention_pct
FROM user_months AS m
JOIN cohort_size AS s ON s.signup_month = m.signup_month
GROUP BY m.signup_month, m.month_number, s.users;

Key points:

• DISTINCT in user_months means a user who was active 20 times in February counts once for February. Retention counts people, not events.
• The denominator is the full cohort, from users, not just the users who appear in activity. Someone who signed up and never came back must still be in the denominator, or retention looks better than it is.
• * 100.0 avoids integer division.

Definitions are business decisions

Before writing the query, agree with the stakeholder on:

• What starts a cohort: signup, first purchase, or the first paid month?
• What counts as active: any login, a purchase, or a key action like "created a project"?
• Classic vs rolling retention: classic counts users active in month N; rolling ("unbounded") counts users active in month N or later. They answer different questions, and they give different numbers.
• Period length: weekly cohorts suit apps with daily use; monthly or quarterly suit slower purchase cycles.

Write the chosen definitions as comments at the top of the query so the next reader knows what the numbers mean.

Common mistakes

• Counting events instead of users (forgetting DISTINCT).
• Using only active users as the denominator, which hides churn.
• Comparing a young cohort's month 2 with an old cohort's month 2 when the young cohort's month 2 is not finished yet. Exclude the current, partial month.
• Mixing time zones at month boundaries when timestamps are in UTC but the business reports in local time.
• Subtracting '2026-03' - '2026-01' as text. Convert to the year × 12 + month number first.

Quick recap

• A cohort is a group of users who started in the same period; retention = active users in period N / cohort size.
• Build it in steps: cohort per user, month number per activity, distinct count, divide by cohort size.
• Produce the long form in SQL and pivot it in a BI tool.
• Agree on the definitions first and write them in the query.

Try it

Open the Do tab and build the long-form retention table for three monthly cohorts.