Cohort and Retention Analysis in SQL

Cohort analysis groups users by a shared starting event, such as signup month, and tracks how many remain active over time. A single blended retention number hides whether your product is improving. A cohort table shows exactly when users leave and whether recent signups behave better than older ones. This guide builds a full retention pipeline in SQL, from raw event tables to a pivoted retention matrix and a heatmap.

Quick Takeaways

  • Cohort = users grouped by a first-event date (signup, first purchase). Retention = share of that cohort active in period N after the start.
  • Build it in four steps: assign cohorts, compute activity periods, calculate the period offset, divide by cohort size.
  • Use classic (bounded) retention for “active exactly on day N.” Use rolling (unbounded) retention for “active on or after day N.”
  • Always divide by cohort size (all users in the cohort), not by period-0 active users, or your percentages will be inflated.
Retention Type Definition Best For Typical Result
Classic (N-day) Active exactly on day N Daily-use apps (social, games) Lower, spiky
Range (bracket) Active within a window (e.g., days 7–13) Weekly-use products Moderate
Rolling (unbounded) Active on or after day N Low-frequency products (travel, B2B) Higher, smooth

Data Model and Assumptions

All queries use PostgreSQL syntax. Two tables are enough:

-- One row per user, with the cohort-defining event
CREATE TABLE users (
    user_id      BIGINT PRIMARY KEY,
    signup_date  DATE NOT NULL
);

-- One row per activity event (login, purchase, session start)
CREATE TABLE events (
    event_id    BIGINT PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES users(user_id),
    event_time  TIMESTAMP NOT NULL
);

-- Index to keep joins and date filters fast at scale
CREATE INDEX idx_events_user_time ON events (user_id, event_time);

Define “active” before you write any SQL. A login, a purchase, and a pageview produce three different retention curves. Pick the event that represents real product value and keep it consistent across reports.

Step 1: Assign Users to Monthly Cohorts

Truncate the signup date to the month. DATE_TRUNC(‘month’, …) collapses every date in a month to the first day, which gives you a clean cohort key.

SELECT
    user_id,
    DATE_TRUNC('month', signup_date)::date AS cohort_month
FROM users;

Choose cohort granularity to match your product cycle. Daily cohorts suit consumer apps with fast feedback loops. Weekly cohorts balance noise and speed. Monthly cohorts suit subscription and B2B products.

Step 2: Compute Activity Periods Per User

Deduplicate activity to one row per user per month. This prevents a user with 50 sessions from being counted 50 times.

SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_time)::date AS activity_month
FROM events;

SELECT DISTINCT is the simplest approach. At larger volumes, replace it with a GROUP BY user_id, activity_month inside a CTE, which is easier for planners to optimize.

Step 3: Calculate the Period Offset

The offset (month_number) is the gap between the activity month and the cohort month. Month 0 is the signup month, month 1 is the following month, and so on.

WITH cohorts AS (
    SELECT user_id,
           DATE_TRUNC('month', signup_date)::date AS cohort_month
    FROM users
),
activity AS (
    SELECT DISTINCT user_id,
           DATE_TRUNC('month', event_time)::date AS activity_month
    FROM events
),
joined AS (
    SELECT
        c.user_id,
        c.cohort_month,
        a.activity_month,
        -- Convert both dates to a month index, then subtract
        (EXTRACT(YEAR  FROM a.activity_month) * 12 + EXTRACT(MONTH FROM a.activity_month))
      - (EXTRACT(YEAR  FROM c.cohort_month)   * 12 + EXTRACT(MONTH FROM c.cohort_month))
        AS month_number
    FROM cohorts c
    JOIN activity a ON a.user_id = c.user_id
    WHERE a.activity_month >= c.cohort_month   -- drop pre-signup noise
)
SELECT * FROM joined LIMIT 10;

The WHERE filter removes events that predate the signup record, which happen with backfilled or migrated data. Check how many rows it drops; a large number signals a data quality problem.

Step 4: Build the Retention Table (Long Format)

Count distinct active users per cohort and offset, then divide by cohort size.

WITH cohorts AS (
    SELECT user_id,
           DATE_TRUNC('month', signup_date)::date AS cohort_month
    FROM users
),
activity AS (
    SELECT DISTINCT user_id,
           DATE_TRUNC('month', event_time)::date AS activity_month
    FROM events
),
joined AS (
    SELECT
        c.user_id,
        c.cohort_month,
        ((EXTRACT(YEAR FROM a.activity_month) * 12 + EXTRACT(MONTH FROM a.activity_month))
       - (EXTRACT(YEAR FROM c.cohort_month)   * 12 + EXTRACT(MONTH FROM c.cohort_month)))::int
        AS month_number
    FROM cohorts c
    JOIN activity a ON a.user_id = c.user_id
    WHERE a.activity_month >= c.cohort_month
),
cohort_size AS (
    SELECT cohort_month, COUNT(*) AS cohort_users
    FROM cohorts
    GROUP BY cohort_month
)
SELECT
    j.cohort_month,
    cs.cohort_users,
    j.month_number,
    COUNT(DISTINCT j.user_id) AS active_users,
    ROUND(100.0 * COUNT(DISTINCT j.user_id) / cs.cohort_users, 1) AS retention_pct
FROM joined j
JOIN cohort_size cs USING (cohort_month)
GROUP BY j.cohort_month, cs.cohort_users, j.month_number
ORDER BY j.cohort_month, j.month_number;

This long format is ideal for BI tools and for pandas. Each row holds one cohort-period cell. The denominator is cohort_users, the full cohort, so month 0 may land below 100% if some users never triggered an activity event.

Step 5: Pivot Into a Retention Matrix

Analysts prefer a triangle: cohorts as rows, months as columns. PostgreSQL’s FILTER clause makes conditional aggregation readable.

-- Reuse the CTEs from Step 4 (cohorts, activity, joined, cohort_size), then:
SELECT
    j.cohort_month,
    cs.cohort_users,
    ROUND(100.0 * COUNT(DISTINCT j.user_id) FILTER (WHERE month_number = 0) / cs.cohort_users, 1) AS m0,
    ROUND(100.0 * COUNT(DISTINCT j.user_id) FILTER (WHERE month_number = 1) / cs.cohort_users, 1) AS m1,
    ROUND(100.0 * COUNT(DISTINCT j.user_id) FILTER (WHERE month_number = 2) / cs.cohort_users, 1) AS m2,
    ROUND(100.0 * COUNT(DISTINCT j.user_id) FILTER (WHERE month_number = 3) / cs.cohort_users, 1) AS m3,
    ROUND(100.0 * COUNT(DISTINCT j.user_id) FILTER (WHERE month_number = 6) / cs.cohort_users, 1) AS m6
FROM joined j
JOIN cohort_size cs USING (cohort_month)
GROUP BY j.cohort_month, cs.cohort_users
ORDER BY j.cohort_month;

Cells for periods that have not happened yet return NULL. Keep them blank rather than showing 0%. A zero implies churn, while a blank means “too early to tell.”

Dialect Differences

Task PostgreSQL BigQuery MySQL 8 Snowflake
Truncate to month DATE_TRUNC(‘month’, d) DATE_TRUNC(d, MONTH) DATE_FORMAT(d,’%Y-%m-01′) DATE_TRUNC(‘month’, d)
Month difference Year×12 + month math DATE_DIFF(a, b, MONTH) TIMESTAMPDIFF(MONTH, b, a) DATEDIFF(‘month’, b, a)
Conditional count FILTER (WHERE …) COUNTIF(…) SUM(CASE …) COUNT_IF(…)
Distinct count at scale Exact APPROX_COUNT_DISTINCT Exact APPROX_COUNT_DISTINCT

Day-N Retention for Daily Products

Monthly buckets are too coarse for apps where users return daily. Compute classic D1, D7, and D30 retention with date arithmetic.

WITH daily_activity AS (
    SELECT DISTINCT user_id, event_time::date AS event_date
    FROM events
)
SELECT
    u.signup_date AS cohort_date,
    COUNT(DISTINCT u.user_id) AS cohort_users,
    ROUND(100.0 * COUNT(DISTINCT d.user_id) FILTER (WHERE d.event_date = u.signup_date + 1)
          / COUNT(DISTINCT u.user_id), 1) AS d1_pct,
    ROUND(100.0 * COUNT(DISTINCT d.user_id) FILTER (WHERE d.event_date = u.signup_date + 7)
          / COUNT(DISTINCT u.user_id), 1) AS d7_pct,
    ROUND(100.0 * COUNT(DISTINCT d.user_id) FILTER (WHERE d.event_date = u.signup_date + 30)
          / COUNT(DISTINCT u.user_id), 1) AS d30_pct
FROM users u
LEFT JOIN daily_activity d ON d.user_id = u.user_id
GROUP BY u.signup_date
ORDER BY u.signup_date;

The LEFT JOIN keeps users who never returned in the denominator. An INNER JOIN would silently drop them and overstate retention.

Rolling (Unbounded) Retention

Swap the equality test for a comparison against each user’s last activity date:

WITH last_seen AS (
    SELECT user_id, MAX(event_time)::date AS last_active
    FROM events
    GROUP BY user_id
)
SELECT
    u.signup_date,
    COUNT(*) AS cohort_users,
    ROUND(100.0 * COUNT(*) FILTER (WHERE l.last_active >= u.signup_date + 7)  / COUNT(*), 1) AS rolling_d7,
    ROUND(100.0 * COUNT(*) FILTER (WHERE l.last_active >= u.signup_date + 30) / COUNT(*), 1) AS rolling_d30
FROM users u
LEFT JOIN last_seen l ON l.user_id = u.user_id
GROUP BY u.signup_date
ORDER BY u.signup_date;

Rolling retention never rises as N grows. It only flattens or falls, which makes it a stable metric for low-frequency products.

Visualize the Retention Matrix in Python

Pull the long-format result into pandas and render a heatmap.

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg2://user:password@host:5432/db")

# Paste the Step 4 query into this string
query = open("retention_long.sql").read()
df = pd.read_sql(query, engine)

# Pivot: rows = cohort, columns = months since signup
matrix = df.pivot(index="cohort_month", columns="month_number", values="retention_pct")

plt.figure(figsize=(12, 7))
sns.heatmap(
    matrix,
    annot=True, fmt=".0f",       # show rounded percentages in each cell
    cmap="Blues",
    vmin=0, vmax=100,
    cbar_kws={"label": "Retention %"},
)
plt.title("Monthly Cohort Retention")
plt.xlabel("Months Since Signup")
plt.ylabel("Cohort Month")
plt.tight_layout()
plt.savefig("retention_heatmap.png", dpi=150)

Read the heatmap in two directions. Reading across a row shows the decay curve of one cohort. Reading down a column compares cohorts at the same age, which is how you detect whether product changes improved retention.

Real-World Use Cases

Predicting Churn Risk for a SaaS Product

A B2B SaaS team defines the cohort as signup month and “active” as any session with a core feature. The matrix shows month-1 retention falling from 62% to 48% for cohorts after a pricing change. The team then splits cohorts by acquisition channel (add channel to the cohorts CTE and GROUP BY) and finds paid-social users drive the drop.

Measuring Onboarding Impact in a Mobile App

An app ships a new onboarding flow on March 1. Weekly cohorts before and after the date are compared on D1 and D7 retention. A lift in D1 with a flat D7 suggests the flow improves first-session success but not long-term habit formation.

Repeat Purchase Analysis in E-commerce

Define the cohort by first order month and activity by any later order. The matrix becomes a repeat-purchase curve. Add SUM(order_value) instead of COUNT(DISTINCT user_id) to produce revenue retention, which reveals whether surviving customers spend more over time.

Common Pitfalls and Fixes

Pitfall Symptom Fix
Dividing by period-0 actives Month 0 always shows 100% Divide by full cohort_users
INNER JOIN to events Retention inflated Use LEFT JOIN from users
Counting events, not users Retention above 100% Use COUNT(DISTINCT user_id)
Incomplete current period Latest cohort looks like it churned Exclude or flag the in-progress month
Timezone mismatch Day-N counts shift by one Convert to one timezone before ::date
Tiny cohorts Wild percentage swings Show cohort_users beside every row

Performance Tips for Large Event Tables

  • Pre-aggregate events into a daily or monthly user-activity table. Joining billions of raw events on every refresh is wasteful.
  • Partition the events table by month so date filters prune partitions.
  • Materialize the retention result with a scheduled job, and let dashboards read the small output table.
  • Use APPROX_COUNT_DISTINCT on warehouses that support it when exact counts are not required.

Strategic FAQ

What is the difference between cohort analysis and retention analysis?

Cohort analysis is the grouping method: it segments users by a shared start event or attribute. Retention analysis is the metric applied to those groups: it measures the share of each cohort still active after a given period. Retention cohort tables combine both.

How do you calculate retention rate in SQL?

Assign each user a cohort date, compute the period offset between each activity date and the cohort date, count distinct active users per offset, and divide by cohort size. Multiply by 100 for a percentage. Use COUNT(DISTINCT user_id) with a LEFT JOIN from the users table.

What is a good retention rate?

It depends on product type. Consumer mobile apps commonly see low double-digit D30 retention, while B2B SaaS products often retain well over half of customers after the first month. Compare your own cohorts against each other over time rather than chasing a universal benchmark.

Should I use classic or rolling retention?

Use classic retention for products with a natural daily or weekly usage rhythm, since it shows exact return behavior. Use rolling retention for products people use irregularly, such as travel booking or tax software, where a missed day does not mean churn.

Hot this week

Polars for Pandas Users: A Practical Migration Guide

Migrate from Pandas to Polars with side-by-side code, expressions, and lazy evaluation. Follow this guide and speed up your pipelines today.

SQL for Time Series: Date Functions, Rolling Averages, and Gaps

Master SQL time series analysis: date functions, rolling averages, and gap filling with copy-ready PostgreSQL code. Start querying smarter today.

Build a RAG App From Scratch: Documents, Embeddings, and Answers

Build a RAG app from scratch in Python: chunk documents, create embeddings, retrieve context, and generate cited answers. Copy the full code now.

SHAP vs LIME: Explain Any Model’s Predictions

Compare SHAP vs LIME for explaining ML predictions. See code, speed tradeoffs, and a decision guide. Pick the right explainer for your model today.

Predict House Prices With Linear Regression: A Complete Project

Build a house price predictor with linear regression in Python. Clean data, train, and evaluate with RMSE and R². Start the full project now.

Topics

Polars for Pandas Users: A Practical Migration Guide

Migrate from Pandas to Polars with side-by-side code, expressions, and lazy evaluation. Follow this guide and speed up your pipelines today.

SQL for Time Series: Date Functions, Rolling Averages, and Gaps

Master SQL time series analysis: date functions, rolling averages, and gap filling with copy-ready PostgreSQL code. Start querying smarter today.

Build a RAG App From Scratch: Documents, Embeddings, and Answers

Build a RAG app from scratch in Python: chunk documents, create embeddings, retrieve context, and generate cited answers. Copy the full code now.

SHAP vs LIME: Explain Any Model’s Predictions

Compare SHAP vs LIME for explaining ML predictions. See code, speed tradeoffs, and a decision guide. Pick the right explainer for your model today.

Predict House Prices With Linear Regression: A Complete Project

Build a house price predictor with linear regression in Python. Clean data, train, and evaluate with RMSE and R². Start the full project now.

AI and Copyright: The Lawsuits, the Rulings, and What They Mean for Creators

Track every major AI copyright lawsuit in 2026: Anthropic's $1.5B deal, NYT v. OpenAI, Ross, Midjourney. See what it means for your work. Read now.

Build Your First Spam Email Classifier

Build a spam email classifier in Python with TF-IDF and Naive Bayes. Copy the code, hit 98%+ accuracy, and ship your first ML model today.

Best AI Music and Voice Generators in 2026

Compare Suno v6, ElevenLabs Music, Udio, Eleven v4, Cartesia and Fish Audio on price, rights and latency. Pick your tool and start creating today.

Related Articles

Popular Categories