How to Calculate Retention Rates Using SQL: A Complete Guide for Data Engineers

Calculate retention rates in SQL by building a user-activity accounting table, deriving days since first activity, and dividing active users by cohort size.

Retention analysis is foundational for measuring product health and user engagement. This guide walks through production-ready SQL patterns from the DataExpert-io/data-engineer-handbook repository to compute retention rates directly in your data warehouse without external tools.

The Three-Step Retention Pattern

The approach implemented in retention_analysis.sql follows a clear analytical workflow: create an accounting table, align users to a cohort timeline, and compute percentage active. This pattern scales from daily snapshots to weekly or monthly reporting.

Step 1: Build the User Growth Accounting Table

The foundation is the users_growth_accounting table defined in intermediate-bootcamp/materials/4-applying-analytical-patterns/tables/user_growth_accounting.sql. This table stores one row per user per day with their activity classification.

CREATE TABLE users_growth_accounting (
    user_id               TEXT,
    first_active_date    DATE,
    last_active_date     DATE,
    daily_active_state   TEXT,
    weekly_active_state  TEXT,
    dates_active         DATE[],
    date                 DATE,
    PRIMARY KEY (user_id, date)
);

Key columns:

• first_active_date — anchors each user to their cohort
• daily_active_state — classifies activity as New, Retained, Resurrected, Dormant, or Churned
• date — the snapshot date for the user's state

Step 2: Derive Days Since First Active

Retention comparison requires aligning all users to the same relative timeline. The query in retention_analysis.sql computes this with simple date arithmetic:

date - first_active_date AS days_since_first_active

This creates a cohort-aligned index where day 0 is signup, day 1 is the first full day after, and so on.

Step 3: Calculate Percentage Active

The core retention metric divides active users by total cohort members. The SQL counts users whose daily_active_state indicates engagement:

SELECT
       date - first_active_date AS days_since_first_active,
       CAST(COUNT(CASE
           WHEN daily_active_state IN ('Retained', 'Resurrected', 'New')
                THEN 1 END) AS REAL) / COUNT(1) AS pct_active,
       COUNT(1) AS users_in_cohort
FROM users_growth_accounting
GROUP BY date - first_active_date
ORDER BY days_since_first_active;

How the calculation works:

• Numerator: Users with states Retained, Resurrected, or New (inactive states Dormant and Churned are excluded)
• Denominator: COUNT(1) — total users with records at that day-offset
• Cast to REAL: Prevents integer division and ensures decimal precision

Interpreting Retention Results

The query output maps directly to standard retention curves:

days_since_first_active	Meaning	Typical pct_active pattern
0	Signup day	Near 100% (all users are New)
1	First return opportunity	First major drop-off point
7	One-week retention	Key SaaS health indicator
30	Monthly retention	Long-term engagement signal

Plotting pct_active against days_since_first_active yields the classic retention curve used by product teams to identify friction points.

Adapting to Different Time Grains

To calculate weekly retention rates using SQL, modify the date arithmetic:

-- Weekly cohort alignment
FLOOR((date - first_active_date) / 7) AS weeks_since_first_active

For monthly retention, use DATE_TRUNC:

-- Monthly cohort alignment
DATE_DIFF('month', first_active_date, date) AS months_since_first_active

Adjust the daily_active_state filter to weekly_active_state when using weekly snapshots.

Production Considerations

When deploying this pattern at scale:

• Index on (first_active_date, date) for efficient cohort filtering
• Partition by date for incremental daily processing
• Validate users_in_cohort — large deviations indicate data quality issues
• **Mark churned users explicitly rather than inferring from absence; the user_growth_accounting table handles this through state machine logic

Summary

• Retention rates measure cohort survival over time — critical for product analytics
• The three-step SQL pattern from DataExpert-io/data-engineer-handbook: build accounting table → align to cohort timeline → compute active percentage
• Use daily_active_state filtering to include only engaged user states
• days_since_first_active creates comparable cohort timelines regardless of absolute calendar date
• Adapt date arithmetic for weekly or monthly retention analysis as needed

Frequently Asked Questions

What is a user growth accounting table?

A user growth accounting table is a stateful snapshot that classifies each user's activity status on every date they appear in the system. It enables cohort-based retention analysis by tracking when users are New, Retained, Resurrected, Dormant, or Churned — rather than simply counting raw events.

Why cast the numerator to REAL in the retention calculation?

Without CAST(... AS REAL), SQL performs integer division, truncating decimals to zero. For example, 3/10 returns 0 in many databases; CAST(3 AS REAL)/10 returns 0.3. The retention_analysis.sql query explicitly casts to preserve percentage precision.

How do I handle users with sporadic activity?

The state machine in user_growth_accounting.sql accounts for this through the Resurrected state — users who return after being Dormant. These users count as active in retention calculations, distinguishing true re-engagement from measurement noise.

Can this pattern work in BigQuery, Snowflake, or Redshift?

Yes. The core pattern is ANSI SQL compatible. Adjust date functions as needed: DATE_DIFF syntax varies by warehouse, and some platforms require SAFE_CAST or ::FLOAT instead of CAST(... AS REAL). The logic in intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/retention_analysis.sql ports directly to modern cloud warehouses.

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