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

> Learn to calculate retention rates using SQL with this expert guide. Build activity tables, track user cohorts, and analyze engagement for data engineering success.

- Repository: [DataExpert.io/data-engineer-handbook](https://github.com/DataExpert-io/data-engineer-handbook)
- Tags: how-to-guide
- Published: 2026-08-06

---

**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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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.

```sql
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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/retention_analysis.sql) computes this with simple date arithmetic:

```sql
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:

```sql
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:

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

```

For **monthly retention**, use `DATE_TRUNC`:

```sql
-- 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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/retention_analysis.sql) ports directly to modern cloud warehouses.