How to Implement Retention Analysis in SQL: A Complete Cohort Analysis Guide

Retention analysis measures how long users continue engaging with a product after their first interaction by grouping users into cohorts based on acquisition date and calculating return rates using SQL date functions and window functions.

The DataExpert-io/data-engineer-handbook repository provides the foundational SQL patterns and dimensional modeling concepts required to implement retention analysis in SQL across PostgreSQL, Snowflake, BigQuery, Redshift, and Databricks. This technique, commonly called cohort analysis, enables data engineers to track user engagement timelines and identify product stickiness through standard ANSI-SQL queries.

What Is Retention (Cohort) Analysis?

Retention analysis tracks user engagement over discrete time periods measured from a starting event. You group users into cohorts based on their acquisition date—typically the day they first signed up or performed a key action—then calculate what percentage of each cohort returns on subsequent days, weeks, or months.

According to the DataExpert-io/data-engineer-handbook source code, this analysis relies on dimensional modeling principles covered in intermediate-bootcamp/materials/1-dimensional-data-modeling/README.md, where fact tables store user events and dimension tables define user attributes. The repository's intermediate-bootcamp/materials/4-applying-analytical-patterns/README.md specifically covers the window functions and date arithmetic essential for these calculations.

Step-by-Step SQL Implementation

The following query implements a daily retention analysis using five Common Table Expressions (CTEs). This pattern works in any ANSI-compliant data warehouse.

Step 1: Define the Cohort

First, identify each user's acquisition date using MIN() and GROUP BY. This establishes the cohort_date—the baseline from which you measure all subsequent activity.

WITH first_touch AS (
    SELECT
        user_id,
        MIN(event_date) AS cohort_date
    FROM events
    WHERE event_name = 'signup'
    GROUP BY user_id
)

In intermediate-bootcamp/materials/4-applying-analytical-patterns/README.md, this approach aligns with analytical SQL patterns for identifying first occurrences within partitioned datasets.

Step 2: Build the Activity Timeline

Join the cohort dates back to the events table and calculate the day offset for every subsequent user action using DATE_TRUNC and DATE_DIFF.

user_activity AS (
    SELECT
        e.user_id,
        e.event_date,
        DATE_TRUNC('day', e.event_date) AS activity_day,
        f.cohort_date,
        DATE_DIFF('day', f.cohort_date, e.event_date) AS days_since_cohort
    FROM events e
    JOIN first_touch f ON e.user_id = f.user_id
    WHERE e.event_name = 'login'
)

This CTE creates the timeline structure necessary for period-over-period comparisons, a concept reinforced in the handbook's data modeling materials.

Step 3: Aggregate Active Users per Period

Count distinct users for each cohort and day combination to determine how many users from each starting cohort were active on each subsequent day.

cohort_retention AS (
    SELECT
        cohort_date,
        days_since_cohort,
        COUNT(DISTINCT user_id) AS active_users
    FROM user_activity
    GROUP BY cohort_date, days_since_cohort
)

The COUNT(DISTINCT) operation here is fundamental to accurate retention metrics, ensuring you count each user only once per period regardless of how many events they generated.

Step 4: Determine Cohort Sizes

Isolate the Day 0 counts to establish your denominator—the total size of each cohort.

cohort_sizes AS (
    SELECT
        cohort_date,
        active_users AS cohort_size
    FROM cohort_retention
    WHERE days_since_cohort = 0
)

This filter captures all users who performed the target event on their acquisition date, providing the baseline for percentage calculations.

Step 5: Compute Retention Rates

Calculate the final retention percentage by dividing active users by cohort size for each period.

final_retention AS (
    SELECT
        r.cohort_date,
        r.days_since_cohort,
        r.active_users,
        s.cohort_size,
        ROUND(100.0 * r.active_users / s.cohort_size, 2) AS retention_pct
    FROM cohort_retention r
    JOIN cohort_sizes s ON r.cohort_date = s.cohort_date
    ORDER BY r.cohort_date, r.days_since_cohort
)

SELECT * FROM final_retention;

The arithmetic operation 100.0 * r.active_users / s.cohort_size converts the ratio to a percentage, with ROUND() formatting the output to two decimal places for readability.

Creating a Retention Heat Map

To visualize trends, pivot the results into a matrix format using conditional aggregation. This example shows weekly retention buckets:

SELECT
    cohort_date,
    MAX(CASE WHEN days_since_cohort = 0 THEN retention_pct END) AS d0,
    MAX(CASE WHEN days_since_cohort = 7 THEN retention_pct END) AS d7,
    MAX(CASE WHEN days_since_cohort = 14 THEN retention_pct END) AS d14,
    MAX(CASE WHEN days_since_cohort = 21 THEN retention_pct END) AS d21,
    MAX(CASE WHEN days_since_cohort = 28 THEN retention_pct END) AS d28
FROM final_retention
GROUP BY cohort_date
ORDER BY cohort_date;

This pivot structure allows data analysts to scan across rows (cohorts) and columns (time periods) to identify retention drop-off points, as practiced in real-world projects listed in projects.md within the handbook repository.

Summary

• Cohort definition requires identifying the first activity date per user using MIN(event_date) grouped by user_id.
• Timeline construction depends on date functions like DATE_DIFF and DATE_TRUNC to calculate offsets from the cohort start date.
• Retention calculation divides COUNT(DISTINCT user_id) for each period by the total cohort size established on day zero.
• Cross-platform compatibility means these queries run on PostgreSQL, Snowflake, BigQuery, Redshift, and Databricks without modification.
• Dimensional modeling foundations from intermediate-bootcamp/materials/1-dimensional-data-modeling/README.md ensure your fact and dimension tables support efficient retention queries.

Frequently Asked Questions

What SQL functions are essential for retention analysis?

MIN(), DATE_TRUNC, DATE_DIFF, and COUNT(DISTINCT) form the core function set. You use MIN() to establish cohort dates, DATE_TRUNC to normalize timestamps to periods, DATE_DIFF to calculate day offsets, and COUNT(DISTINCT) to accurately tally unique active users per cohort and period.

How do I modify this for weekly or monthly retention instead of daily?

Change the date truncation level and date difference units. Replace DATE_TRUNC('day', ...) with DATE_TRUNC('week', ...) or DATE_TRUNC('month', ...), and adjust DATE_DIFF to use 'week' or 'month' instead of 'day'. The cohort logic remains identical; only the granularity changes.

Can I implement retention analysis in PostgreSQL if it doesn't support DATE_DIFF?

Yes, use the subtraction operator for date differences. In PostgreSQL, subtract two dates directly using (event_date - cohort_date) to get integer days. For BigQuery, use DATE_DIFF(event_date, cohort_date, DAY). The handbook's beginner-bootcamp/introduction.md covers these dialect-specific variations for tools like DataGrip and pgAdmin.

How do I track retention for specific user segments?

Add dimension table joins to your first_touch or user_activity CTEs. Referencing intermediate-bootcamp/materials/1-dimensional-data-modeling/README.md, join user dimension tables containing attributes like region, acquisition channel, or device type. Group by these dimensions alongside cohort_date to calculate segmented retention rates that reveal which user types show higher long-term engagement.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}